name AS ColumnName
,system_type_name AS DataType
WHEN is_nullable=0 THEN ‘NOT NULL’
WHEN is_nullable=1 THEN ‘NULL’
END AS IS_NULLABLE
,column_ordinal AS OrdinalPosition
,(‘Col_’ + CAST(column_ordinal AS varchar(4))) AS Alias
(N'[dbo.Registration]’, null, 0) ;
SQL SERVER – How to retrieve the metadata of a stored procedure
Original author: Muhammad Imran
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
SOLUTION 2 : For SQL Server 2005 and above using OPENROWSET