sp_describe_undeclared_parameters in SQL Server

sp_describe_undeclared_parameters is a system stored procedure introduced in code Denali, SQL Server version 2012 and compatible with all later versions of SQL Server. It returns a result set that contains metadata information about undeclared parameters in a T-SQL batch. I find this sp very useful while dealing with dynamic T-SQL queries. There are many 3rd party tools also available in the market to do the same job but why to use them when we have this great feature available in-house?

This stored procedure have 2 input parameters
@tsql → nvarchar(max) → Mandatory
@params →  nvarchar(max) → Optional

sp_describe_undeclared_parameters

Note: As you can see, in our above query we used a parameter called @is_ms but never declared it. That’s why the sp returned a result set containing the information about undeclared parameter.

Note: The above query will not return anything, because we have the parameter already declared for both the cases.

sp_describe_undeclared_parameters returns the following result set.

Column name Data type Description
parameter_ordinal int NOT NULL Contains the ordinal position of the parameter in the result set. Position of the first parameter will be specified as 1.
name sysname NOT NULL Contains the name of the parameter.
suggested_system_type_id int NOT NULL Contains the system_type_id of the data type of the parameter 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.
suggested_system_type_name nvarchar (256) NULL Contains the data type name. Includes arguments (such as length, precision, scale) specified for the data type of the parameter. If the data type is a user-defined alias type, the underlying system type is specified here. If it is a CLR user-defined data type, NULL is returned in this column. If the type of the parameter cannot be deduced, NULL is returned.
suggested_max_length smallint NOT NULL See sys.columns. for max_length column description.
suggested_precision tinyint NOT NULL See sys.columns. for precision column description.
suggested_scale tinyint NOT NULL See sys.columns. for scale column description.
suggested_user_type_id int NULL For CLR and alias types, contains the user_type_id of the data type of the column as specified in sys.types. Otherwise is NULL.
suggested_user_type_database sysname NULL For CLR and alias types, contains the name of the database in which the type is defined. Otherwise is NULL.
suggested_user_type_schema sysname NULL For CLR and alias types, contains the name of the schema in which the type is defined. Otherwise is NULL.
suggested_user_type_name sysname NULL For CLR and alias types, contains the name of the type. Otherwise is NULL.
suggested_assembly_qualified_type_name nvarchar (4000) NULL For CLR types, returns the name of the assembly and class that defines the type. Otherwise is NULL.
suggested_xml_collection_id int NULL Contains the xml_collection_id of the data type of the parameter as specified in sys.columns. This column will return NULL if the type returned is not associated with an XML schema collection.
suggested_xml_collection_database sysname NULL Contains 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.
suggested_xml_collection_schema sysname NULL Contains 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.
suggested_xml_collection_name sysname NULL Contains 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.
suggested_is_xml_document bit NOT NULL Returns 1 if the type being returned is XML and that type is guaranteed to be an XML document. Otherwise returns 0.
suggested_is_case_sensitive bit NOT NULL Returns 1 if the column is of a case-sensitive string type and 0 if it is not.
suggested_is_fixed_length_clr_type bit NOT NULL Returns 1 if the column is of a fixed-length CLR type and 0 if it is not.
suggested_is_input bit NOT NULL Returns 1 if the parameter is used anywhere other than left side of an assignment. Otherwise returns 0.
suggested_is_output bit NOT NULL Returns 1 if the parameter is used on the left side of an assignment or is passed to an output parameter of a stored procedure. Otherwise returns 0.
formal_parameter_name sysname NULL If the parameter is an argument to a stored procedure or a user-defined function, returns the name of the corresponding formal parameter. Otherwise returns NULL.
suggested_tds_type_id int NOT NULL For internal use.
suggested_tds_length int NOT NULL For internal use.

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

Leave a Reply

%d bloggers like this: