Powershell Where-Object returns zero

sys.dm_exec_describe_first_result_set_for_object (Transact-SQL)

  • 8 minutes to read

Scope of application:SQL Server (all supported versions) Azure SQL database

This dynamic administration function takes an @object_id as a parameter and describes the first result metadata for the module with this 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 is reported in the error columns of the result.

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

Transact-SQL syntax conventions

syntax

Arguments

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

@include_browse_information
@include_browse_information is of type bit. If 1, all queries are parsed as if the FOR BROWSE option were included in the query. Returns additional key columns and source table information.

Returned table

This general metadata is returned in the results metadata as a result set with one row for each column. 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 all control paths, a result set of 0 rows is returned.

Column nameData typeDESCRIPTION
is_hiddenbitIndicates whether this is an additional column for information retrieval purposes that is not displayed in the result set.
column_ordinalintContains the order position of the column in the result set. The position of the first column is specified with 1.
SurnamesysnameContains the name of the column if a name can be determined. Otherwise NULL.
is_nullablebitContains the value 1 if the column is null, 0 if the column is not null, and -1 if it cannot be determined whether the column is null.
system_type_idintContains the system_type_id of the data type of the column, as in sys. types specified. For CLR types, this column returns 240, although the system_type_name column returns NULL.
system_type_namenvarchar (256)Contains the name of the data type. Contains arguments specified for the data type of the column (e.g. length, precision, scale). If the data type is a custom alias type, the underlying system type is specified here. For a CLR custom type, NULL is returned in this column.
max_lengthsmallintMaximum length (in bytes) for the column.

-1 = is the column data type varchar (max), nvarchar (max), varbinary (max) or XML.

At text Column is that max_length Value 16 or the value passed through sp_tableoption 'Text in row' is determined.
precisiontinyintThe precision of the column when based on numeric values. Otherwise 0 is returned.
scaletinyintThe scale of the column when based on numeric values. Otherwise 0 is returned.
collation_namesysnameName of the order of the column, if this is character-based. Otherwise, NULL is returned.
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 NULL.
user_type_databasesysnameFor CLR and alias types, contains the name of the database in which the type was defined. Otherwise NULL.
user_type_schemasysnameFor CLR and alias types, contains the name of the schema in which the type was defined. Otherwise NULL.
user_type_namesysnameFor CLR and alias types, contains the name of the type. Otherwise NULL.
assembly_qualified_type_namenvarchar (4000)For CLR types, returns the name of the assembly and class that define the type. Otherwise NULL.
xml_collection_idintContains the xml_collection_id of the data type for the column as specified in sys.columns. This column returns NULL if the type returned is not associated with an XML schema collection.
xml_collection_databasesysnameContains the database that defines the XML schema collection associated with this type. This column returns NULL if the type returned is not associated with an XML schema collection.
xml_collection_schemasysnameContains the schema that defines the XML schema collection associated with this type. This column returns 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 returns NULL if the type returned is not associated with an XML schema collection.
is_xml_documentbitReturns 1 if the data type returned is XML and the type is guaranteed to be a complete XML document (including a root node), not an XML fragment. Otherwise 0 is returned.
is_case_sensitivebitReturns 1 if the column is of a case-sensitive string type; 0 otherwise.
is_fixed_length_clr_typebitReturns 1 if the column is of a fixed length CLR type, 0 otherwise.
source_serversysnameThe name of the original server returned by the column in this result (for a remote server). The name is like in sys. Servers displayed. Returns NULL if the column is from the local server or the original server could not be determined. Populated only when information search is required.
source_databasesysnameThe name of the original database returned by the column in this result. Returns NULL if the database cannot be determined. Only populates when it is necessary to search for information.
source_schemasysnameThe name of the original schema that is returned by the column in this result. Returns NULL if the schema cannot be determined. Populated only when information search is required.
source_tablesysnameThe name of the original table returned by the column in this result. Returns NULL if the table cannot be determined. Only populates when it is necessary to search for information.
source_columnsysnameThe name of the original column that the column returned in this result. Returns NULL if the column cannot be determined. Only populates when it is necessary to search for information.
is_identity_columnbitReturns 1 if the column is an identity column, otherwise 0. Returns NULL if it cannot be determined whether 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 constraints); otherwise, returns 0. Returns NULL if it cannot be determined whether the column is part of a unique index. Only populates when it is necessary to search for information.
is_updateablebitReturns 1 if the column can be updated; otherwise, returns 0. Returns NULL if it cannot be determined whether the column can be updated.
is_computed_columnbitReturns 1 if the column is a calculated column; otherwise, returns 0. Returns NULL if it cannot be determined whether the column is a calculated column.
is_sparse_column_setbitReturns 1 if the column is a thrift column, otherwise 0. Returns NULL if it cannot be determined whether the column is part of a thrift column set.
ordinal_in_order_by_listsmallintPosition of the column in the ORDER BY list. Returns NULL if the column does not appear in the ORDER BY list or the ORDER BY list cannot be clearly identified.
order_by_list_lengthsmallintThe length of the ORDER BY list. Returns NULL if there is no ORDER BY list or the ORDER BY list cannot be clearly identified. Note that this value is the same for all rows returned by sp_describe_first_result_set.
order_by_is_descendingsmallint NULLIf ordinal_in_order_by_list is not NULL, the order_by_is_descending- Column reported the direction of the ORDER BY clause for that column. Otherwise, NULL is reported.
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 status message returned by the function. If no error has occurred, the column contains NULL.
error_messagenvarchar (4096)Contains the message returned by the function. If there was no error, the column contains NULL.
error_typeintContains an integer that represents the error returned. Is assigned to error_type_desc. See list under Notes.
error_type_descnvarchar (60)Contains a short, uppercase string that represents the error returned. Assigned to error_type. See list under Notes.

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 types of errors and their descriptions.

error_typeerror_typeDESCRIPTION
1MISCAny 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 a dynamic SQL query that could potentially return the first result.
5CLR_PROCEDUREThe result could not be determined because a CLR stored procedure can potentially return the first result.
6CLR_TRIGGERThe result could not be determined because of a dynamic CLR trigger that can potentially return the first result.
7EXTENDED_PROCEDUREThe result could not be determined because an extended stored procedure can potentially return the first result.
8UNDECLARED_PARAMETERThe result could not be determined because the data type of at least one of the columns in the result set 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 from sp_describe_first_result_set is not supported.
11UNSUPPORTED_STATEMENTThe result could not be determined because the batch contains an instruction sent by sp_describe_first_result_set (e.g., FETCH, REVERT, etc.) is not supported.
12OBJECT_ID_NOT_SUPPORTEDThe @object_id given to the function is not supported (i.e. not a stored procedure).
13OBJECT_ID_DOES_NOT_EXISTThe @object_id given to the function was not found in the system catalog.

Permissions

Requires permission to run the @tsql argument.

Examples

A. Returning metadata with and without search information

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

B. Combine the sys.dm_exec_describe_first_result_set_for_object function with a table or view

In the following example the system catalog view sys. Procedures and the sys.dm_exec_describe_first_result_set_for_object Function used to generate metadata for the result sets of all stored procedures in the AdventureWorks2012 Database.

additional Information

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

Is this page helpful?