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.
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.
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.
4) Query the column names (SQL Server)
5) Or this query (SQL Server)
6) And another way (SQL Server)
7) Yet another way (SQL Server)
8) And finally let's not forget Oracle:
Written by Montreal DBA Team