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