Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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

5 Answers 5

up vote 11 down vote accepted

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.

share|improve this answer
    
+1. Also, I never used to copy the headers, because I didn't know this setting. –  StanleyJohns Jul 15 '11 at 20:04

I see that you clearly stated your 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 & SSMS 2008R2)

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.

share|improve this answer
    
Oh ya PS would be fine. –  Kyle Brandt Jul 14 '11 at 13:55
    
+1, handy PS TSQL script to get SQL output to file. –  StanleyJohns Jul 18 '11 at 18:43

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

share|improve this answer

You could do a union all join.

share|improve this answer
    
How, precisely, does that help for the CSV output? I'm just curious since this answer is so short. –  jcolebrand Jul 17 '11 at 21:12
    
@jcolebrand I assume the submitter intended the query to explicitly add the column headers values in as a single row select unioned against the real rows -> SELECT 'col1' AS col1... UNION ALL SELECT T.col1... FROM TABLE T Biggest pain with this approach is the forced cast to character data type in the second select. –  billinkc Jul 19 '11 at 13:35
    
Ahhh, good point. –  jcolebrand Jul 19 '11 at 15:09

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.

share|improve this answer
    
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
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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