Save Results as CSV *with* headers in SSMS

Question

With SQL Server 2008 R2, when I save the results as a CSV there are no headers. I can work around this by copying and pasting with the "Copy with Headers" , or just grab the headers and paste them in the CSV etc, but is there a better way to do this?

Answer 1

In SSMS you need to change some options:

Tools - Options - Query results - sql server - results to grid (or text) -> Include column headers when copying or saving the results.

Changed settings are applied to new, but not existing query windows.

Answer 2

I see that you clearly stated you're looking for a solution in SSMS but I thought I would provide a PowerShell solution in case it helps (SQLPS is accessible from inside of SSMS 2008 & 2008 R2).

You can use SQLPS (or regular PowerShell with the SQL cmdlet snapin) to run something like this:

Invoke-Sqlcmd -Query "sp_databases" -ServerInstance "LocalHost\R2" |
Export-Csv -Path "C:\temp\MyDatabaseSizes.csv" -NoTypeInformation

I can keep going with this example if you're interested.

Answer 3

If you like the PowerShell script approach, I have a script that Exports to CSV from SSMS via PowerShell. I like it in so far you can have a dynamic SQL Script, heck you can select whatever text and SSMS passes it to the script as an argument.

Only downside is I haven't found a clever way to pass along the current window's connection. My current work-around is to have different tools set up that vary only in their connection strings i.e. PROD_DW, PROD_DB, TEST_DW...

Answer 4

You could do a union all join to explicitly add column headers in as a single row select unioned against the real rows, for example:

SELECT 'col1' AS col1... 
UNION ALL 
SELECT T.col1... 
FROM TABLE T

The biggest pain with this approach is the forced cast to character data type in the second select.

Answer 5

SSMS isn't a data exporting tool. Use either the Import/Export wizard or BCP both of which were designed as data export tools and will do what you are looking to do.