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 nameData typeDescription
object_idintID of the object to which this parameter belongs.
namesysnameName 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_idintID of parameter. Is unique within the object. If the object is a scalar function, parameter_id = 0 represents the return value.
system_type_idtinyintID of the system type of the parameter.
user_type_idintID 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_lengthsmallintMaximum length of the parameter, in bytes.

-1 = Column data type is varchar(max), nvarchar(max), varbinary(max), or xml.
precisiontinyintPrecision of the parameter if it is numeric-based; otherwise, 0.
scaletinyintScale of the parameter if it is numeric-based; otherwise, 0.
is_outputbit1 = Parameter is output (or return); otherwise, 0.
is_cursor_refbit1 = Parameter is a cursor reference parameter.
has_default_valuebit1 = 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_documentbit1 = Content is a complete XML document.

0 = Content is a document fragment or the data type of the column is not xml.
default_valuesql_variantIf has_default_value is 1, the value of this column is the value of the default for the parameter; otherwise, NULL.
xml_collection_idintIs 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.

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.

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)

Community Additions