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:.
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.
Next Steps
This is a simple little tip, but could save you time and headaches trying to get the column headers copied.
About the author
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.
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
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.
@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.
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"?
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.