Database.ca SQL Server Blog

My Blog

Monday, December 09 2013

Keywords: column headers only, field headers only, get column names, get field names, return metadata only, SQL Server

How to only return column names or metadata (and no data) in SQL Server & Oracle

There are various ways you could run a query and insure that you only return column names.

 

1) SET FMTONLY (SQL Server)
 
Doing so would only return the column names and guaranteed not to process/generate/run any data.

 

column headers only, field headers only, get column names, get field names, return metadata only, SQL Server

2) sp_describe_first_result_set (SQL Server 2012 and above)
 
This method would return the complete metadata pertaining to the very first record set returned by the query/stored proc.

 

column headers only, field headers only, get column names, get field names, return metadata only, SQL Server

3) Force the query to return zero rows (SQL Server)
 
Not the preferred method, since the query will actually be running and this might not be desirable for various reasons. But it works never the less. If you want to do this, then I'd strongly recommend only filtering by a clustered index in the Where clause, to drastically minimize the impact, and to enhance performance.

 

column headers only, field headers only, get column names, get field names, return metadata only, SQL Server

4) Query the column names (SQL Server)

 

column headers only, field headers only, get column names, get field names, return metadata only, SQL Server

5) Or this query (SQL Server)

 

column headers only, field headers only, get column names, get field names, return metadata only, SQL Server

6) And another way (SQL Server)

 

column headers only, field headers only, get column names, get field names, return metadata only, SQL Server

7) Yet another way (SQL Server)

 

column headers only, field headers only, get column names, get field names, return metadata only, SQL Server

8) And finally let's not forget Oracle:

 

column headers only, field headers only, get column names, get field names, return metadata only, SQL Server

 

Written by Ramin Haghighat