TechNet
Export (0) Print
Expand All
Collapse the table of content
Expand the table of content
Expand Minimize

sys.dm_exec_describe_first_result_set_for_object (Transact-SQL)

 

Applies To: SQL Server

This dynamic management function takes an @object_id as a parameter and describes the first result metadata for the module with that ID. The @object_id specified can be the ID of a Transact-SQL stored procedure or a Transact-SQL trigger. If it is the ID of any other object (such as a view, table, function, or CLR procedure), an error will be specified in the error columns of the result.

sys.dm_exec_describe_first_result_set_for_object has the same result set definition as sys.dm_exec_describe_first_result_set (Transact-SQL) and is similar to sp_describe_first_result_set (Transact-SQL).

Applies to: SQL Server (SQL Server 2012 through current version), Azure SQL Database.

Topic link icon Transact-SQL Syntax Conventions


sys.dm_exec_describe_first_result_set_for_object 
    ( @object_id , @include_browse_information )

@object_id
The @object_id of a Transact-SQL stored procedure or a Transact-SQL trigger. @object_id is type int.

@include_browse_information
@include_browse_information is type bit. If set to 1, each query is analyzed as if it has a FOR BROWSE option on the query. Returns additional key columns and source table information.

This common metadata is returned as a result set with one row for each column in the results metadata. Each row describes the type and nullability of the column in the format described in the following section. If the first statement does not exist for every control path, a result set with zero rows is returned.

Column nameData typeDescription
is_hiddenbitSpecifies whether the column is an extra column added for browsing information purposes that does not actually appear in the result set.
column_ordinalintContains the ordinal position of the column in the result set. Position of the first column will be specified as 1.
namesysnameContains the name of the column if a name can be determined. Otherwise is NULL.
is_nullablebitContains the value 1 if the column allows NULLs, 0 if the column does not allow NULLs, and 1 if it cannot be determined that the column allows NULLs.
system_type_idintContains the system_type_id of the data type of the column as specified in sys.types. For CLR types, even though the system_type_name column will return NULL, this column will return the value 240.
system_type_namenvarchar(256)Contains the data type name. Includes arguments (such as length, precision, scale) specified for the data type of the column. If the data type is a user-defined alias type, the underlying system type is specified here. If it is a CLR user-defined type, NULL is returned in this column.
max_lengthsmallintMaximum length (in bytes) of the column.

-1 = Column data type is varchar(max), nvarchar(max), varbinary(max), or xml.

For text columns, the max_length value will be 16 or the value set by sp_tableoption 'text in row'.
precisiontinyintPrecision of the column if numeric-based. Otherwise returns 0.
scaletinyintScale of column if numeric-based. Otherwise returns 0.
collation_namesysnameName of the collation of the column if character-based. Otherwise returns NULL.
user_type_idintFor CLR and alias types, contains the user_type_id of the data type of the column as specified in sys.types. Otherwise is NULL.
user_type_databasesysnameFor CLR and alias types, contains the name of the database in which the type is defined. Otherwise is NULL.
user_type_schemasysnameFor CLR and alias types, contains the name of the schema in which the type is defined. Otherwise is NULL.
user_type_namesysnameFor CLR and alias types, contains the name of the type. Otherwise is NULL.
assembly_qualified_type_namenvarchar(4000)For CLR types, returns the name of the assembly and class defining the type. Otherwise is NULL.
xml_collection_idintContains the xml_collection_id of the data type of the column as specified in sys.columns. This column will return NULL if the type returned is not associated with an XML schema collection.
xml_collection_databasesysnameContains the database in which the XML schema collection associated with this type is defined. This column will return NULL if the type returned is not associated with an XML schema collection.
xml_collection_schemasysnameContains the schema in which the XML schema collection associated with this type is defined. This column will return NULL if the type returned is not associated with an XML schema collection.
xml_collection_namesysnameContains the name of the XML schema collection associated with this type. This column will return NULL if the type returned is not associated with an XML schema collection.
is_xml_documentbitReturns 1 if the returned data type is XML and that type is guaranteed to be a complete XML document (including a root node), as opposed to an XML fragment). Otherwise returns 0.
is_case_sensitivebitReturns 1 if the column is of a case-sensitive string type and 0 if it is not.
is_fixed_length_clr_typebitReturns 1 if the column is of a fixed-length CLR type and 0 if it is not.
source_serversysnameName of the originating server returned by the column in this result (if it originates from a remote server). The name is given as it appears in sys.servers. Returns NULL if the column originates on the local server, or if it cannot be determined which server it originates on. Is only populated if browsing information is requested.
source_databasesysnameName of the originating database returned by the column in this result. Returns NULL if the database cannot be determined. Is only populated if browsing information is requested.
source_schemasysnameName of the originating schema returned by the column in this result. Returns NULL if the schema cannot be determined. Is only populated if browsing information is requested.
source_tablesysnameName of the originating table returned by the column in this result. Returns NULL if the table cannot be determined. Is only populated if browsing information is requested.
source_columnsysnameName of the originating column returned by the column in this result. Returns NULL if the column cannot be determined. Is only populated if browsing information is requested.
is_identity_columnbitReturns 1 if the column is an identity column and 0 if not. Returns NULL if it cannot be determined that the column is an identity column.
is_part_of_unique_keybitReturns 1 if the column is part of a unique index (including unique and primary constraint) and 0 if not. Returns NULL if it cannot be determined that the column is part of a unique index. Only populated if browsing information is requested.
is_updateablebitReturns 1 if the column is updateable and 0 if not. Returns NULL if it cannot be determined that the column is updateable.
is_computed_columnbitReturns 1 if the column is a computed column and 0 if not. Returns NULL if it cannot be determined that the column is a computed column.
is_sparse_column_setbitReturns 1 if the column is a sparse column and 0 if not. Returns NULL if it cannot be determined that the column is a part of a sparse column set.
ordinal_in_order_by_listsmallintPosition of this column in ORDER BY list Returns NULL if the column does not appear in the ORDER BY list or if the ORDER BY list cannot be uniquely determined.
order_by_list_lengthsmallintLength of the ORDER BY list. Returns NULL if there is no ORDER BY list or if the ORDER BY list cannot be uniquely determined. Note that this value will be the same for all rows returned by sp_describe_first_result_set.
order_by_is_descendingsmallint NULLIf the ordinal_in_order_by_list is not NULL, the order_by_is_descending column reports the direction of the ORDER BY clause for this column. Otherwise it reports NULL.
error_numberintContains the error number returned by the function. Contains NULL if no error occurred in the column.
error_severityintContains the severity returned by the function. Contains NULL if no error occurred in the column.
error_stateintContains the state message returned by the function. If no error occurred. the column will contain NULL.
error_messagenvarchar(4096)Contains the message returned by the function. If no error occurred, the column will contain NULL.
error_typeintContains an integer representing the error being returned. Maps to error_type_desc. See the list under remarks.
error_type_descnvarchar(60)Contains a short uppercase string representing the error being returned. Maps to error_type. See the list under remarks.

This function uses the same algorithm as sp_describe_first_result_set. For more information, see sp_describe_first_result_set (Transact-SQL).

The following table lists the error types and their descriptions

error_typeerror_typeDescription
1MISCAll errors that are not otherwise described.
2SYNTAXA syntax error occurred in the batch.
3CONFLICTING_RESULTSThe result could not be determined because of a conflict between two possible first statements.
4DYNAMIC_SQLThe result could not be determined because of dynamic SQL that could potentially return the first result.
5CLR_PROCEDUREThe result could not be determined because a CLR stored procedure could potentially return the first result.
6CLR_TRIGGERThe result could not be determined because a CLR trigger could potentially return the first result.
7EXTENDED_PROCEDUREThe result could not be determined because an extended stored procedure could potentially return the first result.
8UNDECLARED_PARAMETERThe result could not be determined because the data type of one or more of the result set’s columns potentially depends on an undeclared parameter.
9RECURSIONThe result could not be determined because the batch contains a recursive statement.
10TEMPORARY_TABLEThe result could not be determined because the batch contains a temporary table and is not supported by sp_describe_first_result_set .
11UNSUPPORTED_STATEMENTThe result could not be determined because the batch contains a statement that is not supported by sp_describe_first_result_set (e.g., FETCH, REVERT etc.).
12OBJECT_ID_NOT_SUPPORTEDThe @object_id passed to the function is not supported (i.e. not a stored procedure)
13OBJECT_ID_DOES_NOT_EXISTThe @object_id passed to the function was not found in the system catalog.

Requires permission to execute the @tsql argument.

A. Returning metadata with and without browse information

The following example creates a stored procedure named TestProc2 that returns two result sets. Then the example demonstrates that sys.dm_exec_describe_first_result_set returns information about the first result set in the procedure, with and without the browse information.

CREATE PROC TestProc2
AS
SELECT object_id, name FROM sys.objects ;
SELECT name, schema_id, create_date FROM sys.objects ;
GO

SELECT * FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('TestProc2'), 0) ;
SELECT * FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('TestProc2'), 1) ;
GO

B. Combining the sys.dm_exec_describe_first_result_set_for_object function and a table or view

The following example uses both the sys.procedures system catalog view and the sys.dm_exec_describe_first_result_set_for_object function to display metadata for the result sets of all stored procedures in the AdventureWorks2012 database.

USE AdventureWorks2012;
GO

SELECT p.name, r.* 
FROM sys.procedures AS p
CROSS APPLY sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r;
GO


sp_describe_first_result_set (Transact-SQL)
sp_describe_undeclared_parameters (Transact-SQL)
sys.dm_exec_describe_first_result_set (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft