sys.all_parameters (Transact-SQL)

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Shows the union of all parameters that belong to user-defined or system objects.

Column name Data type Description
object_id int ID of the object to which this parameter belongs.
name sysname Name of parameter. Is unique within the object. If the object is a scalar function, the parameter name is an empty string in the row representing the return value.
parameter_id int ID of parameter. Is unique within the object. If the object is a scalar function, parameter_id = 0 represents the return value.
system_type_id tinyint ID of the system type of the parameter.
user_type_id int ID of the type of the parameter as defined by the user.

To return the name of the type, join to the sys.types catalog view on this column.
max_length smallint Maximum length of the parameter, in bytes.

-1 = Column data type is varchar(max), nvarchar(max), varbinary(max), or xml.
precision tinyint Precision of the parameter if it is numeric-based; otherwise, 0.
scale tinyint Scale of the parameter if it is numeric-based; otherwise, 0.
is_output bit 1 = Parameter is output (or return); otherwise, 0.
is_cursor_ref bit 1 = Parameter is a cursor reference parameter.
has_default_value bit 1 = Parameter has a default value.

SQL Server only maintains default values for CLR objects in this catalog view; therefore, this column will always have a value of 0 for Transact-SQL objects. To view the default value of a parameter in a Transact-SQL object, query the definition column of the sys.sql_modules catalog view, or use the OBJECT_DEFINITION system function.
is_xml_document bit 1 = Content is a complete XML document.

0 = Content is a document fragment or the data type of the column is not xml.
default_value sql_variant If has_default_value is 1, the value of this column is the value of the default for the parameter; otherwise, NULL.
xml_collection_id int Is the ID of the XML schema collection used to validate the parameter.

Nonzero if the data type of the parameter is xml and the XML is typed.

0 = There is no XML schema collection, or the parameter is not XML.

Permissions

The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. For more information, see Metadata Visibility Configuration.

See Also

Object Catalog Views (Transact-SQL)
Catalog Views (Transact-SQL)
Querying the SQL Server System Catalog FAQ
sys.parameters (Transact-SQL)
sys.system_parameters (Transact-SQL)