Transact SQL – Stored Procedure – Metadata

Background

In our last post, we spoke of how we can use OpenQuery\Linked Server to seamlessly create a table from a Stored Procedure.

It works, but if truth be told, there are some side effects.

 

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.

Syntax


select *

from sys.dm_exec_describe_first_result_set_for_object

    (
           object_id
         , 0
     ) tblRS

 

Sample


declare @objectName sysname
declare @objectID    int

set @objectName = '[dataEncrypted].[usp_accountHolder_Retrieve]'
set @objectID = object_id(@objectName)

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

Output:

DescribeFirstResultSetForObject

 

Stored Procedure



use master
go

if object_id('[dbo].[sp_ReviewResultSetColumns]') is null
begin

	exec('create procedure [dbo].[sp_ReviewResultSetColumns] as select 1/0 as [shell] ')

end
go

alter procedure [dbo].[sp_ReviewResultSetColumns]
(
	  @object			sysname = null
	, @outputFormat		tinyint = 1
)
as

	/*
		@outputFormat
			a) 1 - Grid
			b) 2 - Text
	*/

	set nocount on;

	declare @CHAR_NEWLINE  varchar(30)
	declare @CHAR_BATCHES  varchar(30)

	declare @objectID    int

	declare @tblCache TABLE
	(
		  [id]						int not null identity(1,1)
	    , [column_ordinal]			int not null
        , name						sysname not null
        , system_type_name			sysname not null
        , max_length				int not null
        , is_nullable				bit
        , is_identity_column		bit
        , is_part_of_unique_key		bit
 	)

	set @CHAR_NEWLINE = char(13) + char(10);
	set @CHAR_NEWLINE = char(10);

	set @CHAR_BATCHES=  char(13) + char(10) + 'go';


	set @objectID = object_id(@object)
 
	insert into @tblCache
	(
		  [column_ordinal]	
		, name				
		, system_type_name	
		, max_length		
		, is_nullable		
		, is_identity_column	
		, is_part_of_unique_key	

	)
	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

	/*
		If Display as a Grid
	*/
	if (@outputFormat =1)
	begin

		select 
				  [column_ordinal]	
				, name				
				, system_type_name	
				, max_length		
				, is_nullable		
				, is_identity_column	
				, is_part_of_unique_key	

		from   @tblCache

	end
	/*
		If Display as a Text
	*/
	else if (@outputFormat =2)
	begin

		select 
				[sql] =
				Stuff
				(
					(
						Select
								', '
								+ [name]
								+ ' '
								+ system_type_name
								+ ' '
								+ case is_nullable
										when 0 then ' not null '
										when 1 then ' null '
										else ' null '
								  end
								+ @CHAR_NEWLINE
						from @tblCache

						order by
								[column_ordinal]	

						For Xml Path('')
					)
					  , 1
					  , 2
					  , ''
				) 
				



	end


go


exec sys.sp_MS_marksystemobject '[dbo].[sp_ReviewResultSetColumns]'
go

 

sys.dm_exec_describe_first_result_set

To review the result set returned by a set of SQL Statements, please use sp_describe_first_result_set

Syntax


exec sp_describe_first_result_set
@tsql = N'{SQL Statements}'

go

 

Sample


exec sp_describe_first_result_set
@tsql = N'exec [dataEncrypted].[usp_accountHolder_Retrieve]'

go

Output

DescribeFirstResultSet

Warnings

Please be sure to indicate that the SQL text passed in is unicode by prefixing the text with an N.

If you do not, you will get the error text pasted below.


Msg 214, Level 16, State 21, Procedure sp_describe_first_result_set, Line 10
Procedure expects parameter '@tsql' of type 'nvarchar(max)'.

 

References

  1. sys.dm_exec_describe_first_result_set_for_object ( Transact-SQL )
  2. sp_describe_first_result_set ( Transact-SQL )

 

Summary

Please keep in mind that the new introspective tools are only available when targeting v2012 and later editions.

One thought on “Transact SQL – Stored Procedure – Metadata

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s