TSQL to return column headers (but no rows)

Question

TSQL to return column headers (but no rows)

Maxer

SSCrazy Eights

Points: 8964
More actions
January 30, 2012 at 7:39 am

#256001

I know I could query the system tables to return all the columns in a table, but what I want to do is effectively:
SELECT * FROM TableName
but return NO ROWS.
Just the names of the columns.
I thought there was TSQL that could do this (row header???) but my Google-fu is failing me.
Thanks!

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

WHERE 1 = 0

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Maxer SSCrazy Eights Points: 8964 More actions · Answer 2

That will just return zero rows from the table.

So in SSMS I would see column headers, but I couldn't actually copy/paste that result into say notepad.

I thought there was an actual command that would return the column names as the SELECT result/recordset?

Paul White SSC Guru Points: 150467 More actions · Answer 3

Maxer (1/30/2012)
I know I could query the system tables to return all the columns in a table, but what I want to do is effectively:

SELECT TOP (0) * FROM TableName

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 4

Maxer (1/30/2012)
That will just return zero rows from the table.
So in SSMS I would see column headers, but I couldn't actually copy/paste that result into say notepad.
I thought there was an actual command that would return the column names as the SELECT result/recordset?

results to text maybe?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Ken McKelvey SSCoach Points: 18842 More actions · Answer 5

If only the metadata is required by an application, then use:

SET FMTONLY ON;

spaghettidba SSC Guru Points: 105732 More actions · Answer 6

Plenty of ways to do it:

1) In SSMS isse SELECT TOP (0) * FROM SomeTable, right click the cell on the left of the fisrt column header (the one with no caption) and select "copy with headers" from the context menu.

2) SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'SomeTableName'

3) Select the table name in the query editor window and press ALT+F1

Does this help?

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105732 More actions · Answer 7

Gianluca Sartori (1/30/2012)
Plenty of ways to do it:
1) In SSMS isse SELECT TOP (0) * FROM SomeTable, right click the cell on the left of the fisrt column header (the one with no caption) and select "copy with headers" from the context menu.

I guess we just found a bug! I just tried this and I get an exception...

-- Gianluca Sartori

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 8

snap 🙂

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Paul White SSC Guru Points: 150467 More actions · Answer 9

Gianluca Sartori (1/30/2012)
I guess we just found a bug! I just tried this and I get an exception...

Same in the latest SQL Server 2012...

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Cadavre SSC-Forever Points: 41690 More actions · Answer 10

Waiting for some code to compile, so here's another methods 😀

DECLARE @SQL AS NVARCHAR(MAX),

--Set your table name here

@TABLE AS NVARCHAR(100) = 'TC70'

SELECT @SQL = COALESCE(@SQL,'') + ',' + CHAR(13) + CHAR(10) +

'MAX(CASE WHEN rn = ' + CAST(rn AS VARCHAR(3)) + ' THEN COLUMN_NAME ELSE NULL END)'

FROM (SELECT COLUMN_NAME, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rn

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = @TABLE) a

SELECT @SQL = 'SELECT' + CHAR(13) + CHAR(10) + STUFF(@SQL,1,3,'') + CHAR(13) + CHAR(10) + 'FROM (SELECT COLUMN_NAME, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rn ' + CHAR(13) + CHAR(10) +

'FROM INFORMATION_SCHEMA.COLUMNS ' + CHAR(13) + CHAR(10) + 'WHERE TABLE_NAME = ''' + @TABLE + ''') a'

EXECUTE sp_executesql @SQL

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

spaghettidba SSC Guru Points: 105732 More actions · Answer 11

SQL Kiwi (1/30/2012)
Gianluca Sartori (1/30/2012)
I guess we just found a bug! I just tried this and I get an exception...
Same in the latest SQL Server 2012...

Weird. Worth filing on Connect?

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105732 More actions · Answer 12

Gianluca Sartori (1/30/2012)
SQL Kiwi (1/30/2012)
Gianluca Sartori (1/30/2012)
I guess we just found a bug! I just tried this and I get an exception...
Same in the latest SQL Server 2012...
Weird. Worth filing on Connect?

Somebody would say so: http://connect.microsoft.com/SQLServer/feedback/details/714743/copy-table-column-headers-from-ssms-2008

🙂

The real question is: worth fixing?

-- Gianluca Sartori

jcb SSCertifiable Points: 6574 More actions · Answer 13

Maxer,

If all you want is copy the columns to a notepad try this:

With the SSMS use the Object Explorer,

Select the desired database,

Open tables and select the target table,

expand the table,

drag columns do a new query,

it ill past the columns names to the nem query

select the text from the new query and past it on notepad or other text editor.

You also can select the table, click the right mouse button and chose Script table as... (insert, select, create, etc.)

Jared SSC Guru Points: 61793 More actions · Answer 14

Switch your results from grid view to text. Copy and paste.

Jared
CE - Microsoft