Search code examples
t-sqlresultset

Getting a Result Set's Column Names via T-SQL

Is there a way to get the column names that an arbitrary query will return using just T-SQL that works with pre-2012 versions of Microsoft SQL Server?

What Doesn't Work:

  • sys.columns and INFORMATION_SCHEMA.COLUMNS work great for obtaining the column list for tables or views but don't work with arbitrary queries.
  • sys.dm_exec_describe_first_result would be perfect except that this management function was added in SQL Server 2012. What I'm writing needs to be backwards compatible to SQL Server 2005.
  • A custom CLR function could easily provide this information but introduces deployment complexities on the server side. I'd rather not go this route.

Any ideas?

Solution

  • So long as the arbitrary query qualifies to be used as a nested query (i.e. no CTEs, unique column names, etc.), this can be achieved by loading the query's metadata into a temp table, then retrieving column details via sys.tables:

    SELECT TOP 0 * INTO #t FROM (query goes here) q
SELECT name FROM tempdb.sys.columns WHERE object_id = OBJECT_ID('tempdb..#t')
DROP TABLE #t

    Thanks to @MartinSmith's for suggesting this approach!