SQL Server Management Studio and Excel – Column Headings on Copy/Paste

Here is a tip about a setting that I think should be on by default, but it isn’t. How many times are you writing T-SQL in SQL Server Management Studio (SSMS) and you need to just copy the results out, paste to Excel and either do more analysis or send off to someone. What happens when you have a result set with 20 columns? 30? 50?

Well, the old way to get the column headings in Excel was to just type them in. Ouch. This gets old realllly fast. But there is a setting in SSMS that most people don’t even know exists, and then once they find out about it, they are like, dang! I wish I would have known about that.

Well, fire up SSMS, Tools->Options, Query Results, SQL Server, Results to Grid. Then check the “Include column headers when copying or saving the results” Hit OK, and the any new query window you have will automatically copy the column headings from your result set with the data!

 

image

Advertisements

23 thoughts on “SQL Server Management Studio and Excel – Column Headings on Copy/Paste”

  1. I have tried this and still the column headings are not included when I do a simple cut and paste. This drives me nuts that column headings are not included by defaul.

    Like

  2. @Cg – sql 2005 right? I have tried this on multiple different boxes and installs and it works. are you just selecting one row, or selecting all of the results? Have you closed and reopened SSMS?

    Like

  3. This doesn't seem to work in Visual Studio Team Edition 2008. There is an option, but even after closing and re-opening VS, the headings still don't copy.It does work in SSMS 2008 though.

    Like

  4. Great tip, is it possible to copy multiple result set in one operation (if you execute 10 select statements it takes time to copy to Excel “one by one”.

    Like

  5. For y’all for whom it’s not working… I *think* I know what it might be.
    There seems to be two kinds of grids in SQL Server Management Studio — the Edit grid and the Results grid — and the “copy-with-headers” deal only works with the Results grid.
    How do you know if you have a Results grid? If the grid has two tabs above it: “Results” and “Messages”. If there are no tabs, you’re in the editor grid.
    If you right-clicked on the table name and said “Open Table” or “Edit Top 200 Rows” (depends on your version), you are in an editor grid, and won’t be able to copy headings. If you clicked on “Select Top 1000 Rows” (in 2008), you have a Results grid. In earlier versions, I think the only way to a Results grid is to open a new query window, select the target database in the dropdown, and execute a query.

    I hope this helps someone!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s