Saving results with headers in Sql Server Management Studio

Question

I am using SQL Server Management Studio    I wish to save the results of a query to an excel file     I choose  save as  and then save to CSV file which I can open in excel  All good except I am missing column headers  any ideas how I get them exported

User · Answer

In SQL Server 2014 Management Studio the setting is at   Tools   Options   Query Results   SQL Server   Results to Text   Include column headers in the result set

User · Answer

Got here when looking for a way to make SSMS properly escape CSV separators when exporting results   Guess what  - this is actually an option  and it is unchecked by default  So by default  you get broken CSV files  and may not even realize it  esp  if your export is large and your data doesn t have commas normally  - and you have to go in and click a checkbox so that your CSVs export correctly   To me  this seems like a monumentally stupid design choice and an apt metaphor for Microsoft s approach to software in general   broken by default  requires meaningless ritualistic actions to make trivial functionality work      But I will gladly donate  100 to a charity of respondent s choice if someone can give me one valid real-life reason for this option to exist  i e   an actual scenario where it was useful

User · Answer

I also face the same issue  When I used right click in the query window and select Query Options  But header rows does not show up in output CSV file    Then I logoff the server  login again and run the script  Then it worked

User · Answer

Try the Export Wizard  In this example I select a whole table  but you can just as easily specify a query            you can also specify a query here

User · Answer

Tools   Options   Query Results   SQL Server   Results to Text  or Grid if you want    Include columns headers in the result set  You might need to close and reopen SSMS after changing this option   On the SQL Editor Toolbar you can select save to file without having to restart SSMS

User · Answer

At least in SQL Server 2012  you can right click in the query window and select Query Options  From there you can select Include Headers for grid and or text and have the Save As work the way you want it without restarting SSMS   You ll still need to change it in Tools- Options in the menu bar to have new query windows use those settings by default

User · Answer

The same problem exists in Visual Studio  here s how to fix it there   Go to   Tools  gt  Options  gt  SQL Server Tools  gt  Transact-SQL Editor  gt  Query Results  gt  Results To Grid   Now click the check box to true   Include column headers when copying or saving the results

User · Answer

The settings which has been advised to change in  Diego s accepted answer might be good if you want to set this option permanently for all future query sessions that you open within SQL Server Management Studio SSMS   This is usually not the case  Also  changing this setting requires restarting SQL Server Management Studio  SSMS  application  This is again a  not-so-nice  experience if you ve many unsaved open query session windows and you are in the middle of some debugging   SQL Server gives a much slick option of changing it on per session basis which is very quick  handy and convenient  I m detailing the steps below using query options window    Right click in query editor window   Click Query Options    at the bottom of the context menu as shown below       Select Results   Grid in the left navigation pane  Check the Include column headers when copying or saving the results check box in right pane as shown below      That s it  Your current session will honour your settings with  immediate effect without restarting SSMS  Also  this setting won t be propagated to any future session  Effectively changing this setting on a per session basis is much less noisy

User · Answer

Select your results by clicking in the top left corner  right click and select  Copy with Headers   Paste in excel  Done

User · Answer

Another possibility is to use the clipboard to copy and paste the results directly into Excel  Just be careful with General type Excel columns  as they can sometimes have unpredictable results  depending on your data  CTL-A anywhere in the result grid  and then right-click     If you have trouble with Excel s General format doing undesired conversions  select the blank columns in Excel before you paste and change the format to  text

[sql] Saving results with headers in Sql Server Management Studio

Examples related to sql

Examples related to sql-server-2008

Examples related to excel

Examples related to ssms

Examples related to export-to-excel