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!
23 replies on “SQL Server Management Studio and Excel – Column Headings on Copy/Paste”
One of the most useful things I’ve read online in weeks. Thanks!
LikeLike
What a great tip – thanks Steve!
Have already passed it around my team. 🙂
LikeLike
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.
LikeLike
@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?
LikeLike
you’re my hero!
LikeLike
Wow, THANK YOU!!!! This has been frustrating me for a LONG time!
LikeLike
How do you set this option when you open a table without a query and then try to copy the table?
LikeLike
@Max I dont think there is an option to do that. Just run a “SELECT *” from your table and copy the results.
LikeLike
Yes, that worked for me. Thank you for your help!
LikeLike
The post is helpful
LikeLike
[…] stumbled across the answer in a blog, wish I’d seen it a while back, anyways: stevienova.com has the answer, (it’s an option in […]
LikeLike
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.
LikeLike
Many thanks!!!!
LikeLike
Amazing! Nice one.
LikeLike
Thank you so much, this was a big-time annoyance
LikeLike
You are my hero!
LikeLike
Freak’n Awesome!
LikeLike
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”.
LikeLike
Try using a UNION to combine the Select statements into one result set.
LikeLike
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!
LikeLike
This described my exact feelings: “Dang! I wish I would have known about that.” Thanks!
LikeLike
Fashzam!! Sweetness! Thanks Thanks!
LikeLike
Is there any way to copy the result set as HTML Table with Header?
LikeLike