SQL Server Portal

In my earlier articles, I wrote about sys.dm_exec_describe_first_result_set
(A new dynamic management view shipped with SQL Server 2012), it is very handy when you need to know the metadata of the first possible result set of any Transact SQL. Today, I came across an issue when I tried to get the metadata of a stored procedure in earlier version of SQL Server and wanted to use the list of columns for further manipulations. However, I could not find a proper solution like sys.dm_exec_describe_first_result_set in the earlier version of SQL Server. So I started developing the solution.

Given below are the solution.

SOLUTION 1 : For SQL Server 2012 and above using sys.dm_exec_describe_first_result_set

USE AdventureWorks2012;
GO
SELECT *  FROM sys.dm_exec_describe_first_result_set
(N'[dbo].[uspGetManagerEmployees] 16', null, 0) ;
--OUTPUT

SOLUTION 2 : For SQL Server 2005 and above using OPENROWSET

USE AdventureWorks2012
GO
--DROP TABLE #temp1
GO
SELECT * INTO  #temp1
FROM OPENROWSET('SQLNCLI', 'Server=(local);uid=sa;pwd=test',
'EXEC AdventureWorks2012.[dbo].[uspGetManagerEmployees] 16');
GO

SELECT * FROM tempdb.sys.columns
WHERE object_id=object_id('tempdb..#temp1')
GO
--View the column name / metadata of the
--above created temporary table.
--OUTPUT