Stored Procedure Metadata - 2012 onwards

Stored Procedure Metadata

In MS SQL Server v2012, Microsoft introduces a couple of new ways to gather meta from Stored Procedure.

The new functions are:

1. sp_describe_first_result_set ( Stored Procedure )
2. sys.dm_exec_describe_first_result_set_for_object ( table value function )

Usage

sys.dm_exec_describe_first_result_set_for_object

To review the result set returned by a Stored Prcoedure, please use sp_describe_first_result_set_for_object; a table value function.

The Stored Procedure’s Object ID should be passed in.

SELECT * FROM sys.dm_exec_describe_first_result_set_for_object (object_id('uspGetBillOfMaterials'),1) SELECT * FROM sys.dm_exec_describe_first_result_set ('EXEC uspGetBillOfMaterials',NULL,1) SELECT * FROM sys.dm_exec_describe_first_result_set ('SELECT * FROM Sales.vStoreWithContacts',NULL,1) select tblRS.column_ordinal , tblRS.name , tblRS.system_type_name , tblRS.max_length , tblRS.is_nullable , tblRS.is_identity_column , is_part_of_unique_key from sys.dm_exec_describe_first_result_set_for_object ( @objectID , 0 ) tblRS order by tblRS.column_ordinal

-- When @browse_information_mode=0, it will give you the meta data but no source data available in this option.
-- When @browse_information_mode=1, it will give you the meta data along with the source info but the source details will be based on this view's table.
-- When @browse_information_mode=2, it will give you the meta data along with the source info but the source details will be based on this view.