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

Question

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?

Answer 1

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!