12

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?

  • Actually, an sp that does this might be ideal. – Kyle Brandt Jul 14 '11 at 12:32
  • Copy with Headers then paste into Excel! – user22106 Apr 3 '13 at 17:20
21

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.

10

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.

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...

1

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.

-1

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.

  • If that were strictly true, it should not have any export option at all. For any software, if you add a feature, make it work right. At least SSMS 2008R2 fails that test when it comes to exporting. Additionally, SSMS actually can satisfy the OP's need. – Eric J. Oct 27 '12 at 18:07
  • 2
    SSMS is an ad-hoc data export tool. If you need a one-off extract of data into, say, Excel then SSMS is the quickest and easiest tool. In the time it takes for the Export Wizard to start up you can execute the query and save the results. – Greenstone Walker Apr 4 '13 at 5:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.