Copy column headers and query results in SQL Server Management Studio

By:   |   Comments (15)   |   Related: > 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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




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

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 (37896)

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 (37891)

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 (37883)

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 9, 2015 - 5:01:41 PM - Pankaj Back To Top (37876)

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 (32252)

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 (26791)

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 (25998)

@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 (25990)

HOLA (Hi)

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

SALUDOS

Wednesday, April 3, 2013 - 12:34:56 PM - Greg Robidoux Back To Top (23142)

@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 3, 2013 - 11:51:44 AM - csmeutah Back To Top (23137)

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 (19067)

thanks. This information is help full

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

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

Wednesday, July 4, 2012 - 11:31:53 AM - Ewan Back To Top (18343)

Thanks for the tip. Very helpful

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

 

Thanks for you tip Greg!!














get free sql tips
agree to terms