Author |
Topic |
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2013-05-29 : 16:22:02
|
I would like to copy and paste the column headings from a sql server table to excel however I don't know how I can get the column names as a result in sql query.I tried this query found from Google but it produces blank output.select column_name from information_schema.columnswhere table_name = "dbo.tablename" |
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-05-29 : 17:06:11
|
make sure your actual table is in the quotes and change the double quotes to 'single' quotes.you can use "paste special" in excel and select "transpose" to paste clip board rows into excel columns. Sort of pivots the results for you.Be One with the OptimizerTG |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-30 : 01:05:20
|
http://beyondrelational.com/modules/2/blogs/70/posts/10846/export-to-excel-with-column-names.aspx------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
|
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2013-06-05 : 15:52:15
|
Thanks Visakh and Bandi.I'm using the solution given by Bandi however this doesn't copy headers when I copy directly data from a table instead of writing a query. Is it possible? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-05 : 23:59:45
|
quote: Originally posted by learning_grsql Thanks Visakh and Bandi.I'm using the solution given by Bandi however this doesn't copy headers when I copy directly data from a table instead of writing a query. Is it possible?
You've to choose copy with headers option. It will copy headers as well.If you were trying to copy from grid showing table data I'm not sure whether it will work correctly.The link speaks only of case of copying results from SSMS query window------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-06 : 00:41:01
|
quote: Originally posted by learning_grsql Thanks Visakh and Bandi.I'm using the solution given by Bandi however this doesn't copy headers when I copy directly data from a table instead of writing a query. Is it possible?
Open SSMS then follow these steps Right-click on Results Tab --> Select all records --> Choose "Copy with Headers" option--Chandu |
|
|
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2013-06-06 : 01:21:44
|
Thanks for confirming this solution works only for query window not for table. Then I assume we cannot copy tables with headers.@Bandi,I'm using SQL Server 2005 and I don't see such an option in either table or query window. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-06 : 01:27:30
|
quote: Originally posted by learning_grsql Thanks for confirming this solution works only for query window not for table. Then I assume we cannot copy tables with headers.@Bandi,I'm using SQL Server 2005 and I don't see such an option in either table or query window.
what do you mean by copying tables with headers? are you referring to grid that shows table data?That option is available only from SQL 2008 SSMSfor 2005 you need to use second method in the linkhttp://blog.sqlauthority.com/2012/09/26/sql-server-copy-column-headers-from-resultset-sql-in-sixty-seconds-027-video/------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
|
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2013-06-06 : 02:26:35
|
Thanks again.When I right click and open table from Object Explorer, the table opens at the right side, I want to copy the whole table with header. Of course, I can write a query "select * from table" and then copy from the resultset with headers using the solutions given by you and Bandi. |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-06 : 02:28:01
|
quote: Originally posted by learning_grsql Thanks again.When I right click and open table from Object Explorer, the table opens at the right side, I want to copy the whole table with header. Of course, I can write a query "select * from table" and then copy from the resultset with headers using the solutions given by you and Bandi.
Welcome--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-06 : 02:29:45
|
quote: Originally posted by learning_grsql Thanks again.When I right click and open table from Object Explorer, the table opens at the right side, I want to copy the whole table with header. Of course, I can write a query "select * from table" and then copy from the resultset with headers using the solutions given by you and Bandi.
welcomeAs told before, its not possible to copy with header from table grid so you've to use select from table way in query window------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Rajan Sahai
Starting Member
8 Posts |
Posted - 2013-06-06 : 11:48:37
|
select column_name from information_schema.columnswhere table_name = 'TableName'The above sql statement works. Have to use single quotes and tablename, not dbo.tablename |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-07 : 01:03:24
|
quote: Originally posted by Rajan Sahai select column_name from information_schema.columnswhere table_name = 'TableName'The above sql statement works. Have to use single quotes and tablename, not dbo.tablename
I think you misunderstoodthe question was to get column headers also along with resultset while copying to excel.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|