Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Copy column headers and query results in SQL Server Management Studio


By:   |   Updated: 2006-11-07   |   Comments (15)   |   Related: More > SQL Server Management Studio

Problem

One of the nice features with SQL Server is the ability to create result sets from queries into a grid result set. This data can then be copied and pasted in other application such as Excel. The downside to saving the results in a grid is that the column headers don't get copied along with the data. To get around this you could query the data in the text format, so you could copy the results along with the column headers, but then you are faced with formatting issues.

Solution

You have the ability to copy the column headers along with the data results. This option gets set using the query options setting. To access this setting from SQL Server Management Studio, select Query > Query Options from the menus and you will see the following screen:.

ssms copy headers

Select the Results / Grid setting and check "Include column headers when copying or saving the results". Once this is set whatever query you run and then if you select the results and copy and paste into another application the column headers are also copied along with the data.

Here is a sample query:

select top 5 name, id, crdate from sysobjects

Results copied with column headers off:

sysrowsetcolumns 4 10/14/05 1:36
sysrowsets 5 10/14/05 1:36
sysallocunits 7 10/14/05 1:36
sysfiles1 8 4/8/03 9:13
syshobtcolumns 13 10/14/05 1:36

Results copied with the column headers on:

name id crdate
sysrowsetcolumns 4 10/14/05 1:36
sysrowsets 5 10/14/05 1:36
sysallocunits 7 10/14/05 1:36
sysfiles1 8 4/8/03 9:13
syshobtcolumns 13 10/14/05 1:36

Another way to do this is to right click on the grid result set and select Copy with Headers or use Ctrl+Shift+C as shown below.

ssms copy headers
Next Steps
  • This is a simple little tip, but could save you time and headaches trying to get the column headers copied.


Last Updated: 2006-11-07


get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, June 30, 2015 - 7:28:47 PM - Pete Back To Top

G'day Greg,

Is it possible to turn headings on or off without using the Query Options dialog?

e.g. I can suppress the row count using:

 

set nocount on

select * from myTable

set nocount off

 

It would be nice if I could do the same thing with column headings.

 

Cheers,

Pete


Thursday, June 11, 2015 - 10:08:21 AM - Greg Robidoux Back To Top

Hi Pankaj,

Not exactly sure how you can do that through Excel.

Take a look at this to see if this could help: http://stackoverflow.com/questions/4190515/pulling-column-names-into-excel-from-sql-query

-Greg

 


Thursday, June 11, 2015 - 12:24:56 AM - PANKAJ KUMAR Back To Top

Thanks for your info!!

However, I would like to inform you that I'm trying to fetch the data from MS SQL server to Excel sheet through Macro. I'm not directly copying the data from excel sheet hence this option will not work here. And hence, I need your suggestion as what I need to add in the macro so that it'll fetch the data with header in excel?


Wednesday, June 10, 2015 - 10:17:05 AM - Greg Robidoux Back To Top

Hi Pankaj,

if you are using a later version of SSMS you can right click on the results grid and select copy with headers.  Have you tried that?

-Greg


Tuesday, June 09, 2015 - 5:01:41 PM - Pankaj Back To Top

I'm using macro to pull the data from SQL server Management Studio. However, it'll pulls the data but without column header. I tried the option given by you-  going to Query option and selected header checkbox but it's not helping me. Still seeing data without header.

 


Sunday, June 15, 2014 - 7:08:21 PM - Michael Back To Top

great tip, but when you have large result sets (I had 70k results), the data columns get skewed...


Friday, September 13, 2013 - 2:27:19 PM - JH Back To Top

Thank you for this, simple solution to a problem I heretofore found only complex recommended solutions.


Thursday, July 25, 2013 - 7:21:47 AM - Greg Robidoux Back To Top

@Alfonso - what version of SQL Server Management Studio are you using?


Wednesday, July 24, 2013 - 6:49:46 PM - Alfonso Perez M Back To Top

HOLA (Hi)

I did what you indicate, but to paste into Excel, do not appear column headers.

SALUDOS


Wednesday, April 03, 2013 - 12:34:56 PM - Greg Robidoux Back To Top

@csmeutah - in SSMS for SQL 2008 and later there are now two options if you right click on the result set.  You can copy the data with the headers and copy the data without the headers. 


Wednesday, April 03, 2013 - 11:51:44 AM - csmeutah Back To Top

Since loading sql2012 Management studio, when I exit the studio it doesn't save the setting; I have to reset it everytime I restart studio.  Is this a "feature"?


Thursday, August 16, 2012 - 8:24:11 AM - Rekha Back To Top

thanks. This information is help full


Friday, July 27, 2012 - 1:25:36 PM - Harinath Back To Top

Wow! awesome solution..!!! I was struggling since many days for writing headers..!!! Thank you very much for the tip...


Wednesday, July 04, 2012 - 11:31:53 AM - Ewan Back To Top

Thanks for the tip. Very helpful


Monday, June 11, 2012 - 8:57:26 AM - Navoday Back To Top

 

Thanks for you tip Greg!!


Learn more about SQL Server tools