Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to get all column names of a table in row?

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.columns
where 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 Optimizer
TG
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-30 : 01:37:15
Refer this link
http://blog.sqlauthority.com/2012/09/26/sql-server-copy-column-headers-from-resultset-sql-in-sixty-seconds-027-video/

--
Chandu
Go to Top of Page

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?
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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
Go to Top of Page

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.

Go to Top of Page

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 SSMS
for 2005 you need to use second method in the link

http://blog.sqlauthority.com/2012/09/26/sql-server-copy-column-headers-from-resultset-sql-in-sixty-seconds-027-video/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-06 : 01:29:12
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.


Check the below link for both MSSQL 2008 & 2005 options
http://blogs.msdn.com/b/varund/archive/2009/04/10/how-to-copy-column-header-in-sql-management-studio-2008.aspx

--
Chandu
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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.


welcome
As 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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Rajan Sahai
Starting Member

8 Posts

Posted - 2013-06-06 : 11:48:37

select column_name from information_schema.columns
where table_name = 'TableName'

The above sql statement works. Have to use single quotes and tablename, not dbo.tablename

Go to Top of Page

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.columns
where table_name = 'TableName'

The above sql statement works. Have to use single quotes and tablename, not dbo.tablename




I think you misunderstood
the question was to get column headers also along with resultset while copying to excel.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -