1. Run the query in SQL Server Management Studio (w/ "Results to Grid")
2. Copy all data from the grid (right-click on the unlabled box at the top left of the grid, choose "copy"), and paste into second row in Excel.
3. Back in SQL, run this query:
SELECT sc.name
FROM syscolumns sc
INNER JOIN sysobjects so
ON so.id = sc.id
WHERE so.name = '[table name]'
ORDER BY sc.colid
4. Paste the results into Excel in a new sheet.
5. Copy the row, then "paste special" --> "transpose" into the first row in the original worksheet, so that they paste as columns.
This will probably break down for very large tables, but I just did this with 96 columns, and 10,000+ rows without issues.
Some more ways to accomplish this:
http://www.mssqltips.com/tip.asp?tip=1202
BTW, not sure why there's not a "Results to .csv format" in SQL Server Management Studio. "Results to Text" seems rather useless...
UPDATE: Dave Kearns points out an even easier way to do this:
In SQL Server Management Studio, go to "Tools" --> "Options" --> "Query Results" --> "SQL Server" --> "Results to Grid" --> check "Include column headers when copying or saving the results"
2 comments:
Copied over from Facebook:
Tools/Options
Query Results/SQL Server/Results to Grid
Check "Include column headers when copying or saving the resutls"... Read More
Voila!
Thanks, Kearns. Works like a charm. Post updated, props given.
Post a Comment