SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to get all column names of a table in row?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

learning_grsql
Posting Yak Master

218 Posts

Posted - 05/29/2013 :  16:22:02  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
5947 Posts

Posted - 05/29/2013 :  17:06:11  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 05/30/2013 :  01:05:20  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 05/30/2013 :  01:37:15  Show Profile  Reply with Quote
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

218 Posts

Posted - 06/05/2013 :  15:52:15  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 06/05/2013 :  23:59:45  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 06/06/2013 :  00:41:01  Show Profile  Reply with Quote
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

218 Posts

Posted - 06/06/2013 :  01:21:44  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 06/06/2013 :  01:27:30  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 06/06/2013 :  01:29:12  Show Profile  Reply with Quote
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

218 Posts

Posted - 06/06/2013 :  02:26:35  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 06/06/2013 :  02:28:01  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 06/06/2013 :  02:29:45  Show Profile  Reply with Quote
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

India
8 Posts

Posted - 06/06/2013 :  11:48:37  Show Profile  Reply with Quote

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

India
52249 Posts

Posted - 06/07/2013 :  01:03:24  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000