sys.parameters (Transact-SQL)
Collapse the table of content
Expand the table of content

sys.parameters (Transact-SQL)


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

Contains a row for each parameter of an object that accepts parameters. If the object is a scalar function, there is also a single row describing the return value. That row will have a parameter_id value of 0.

Column nameData typeDescription
object_idintID of the object to which this parameter belongs.
namesysnameName of the 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 the 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.

Value = -1 when the column data type is varchar(max), nvarchar(max), varbinary(max), or xml.
precisiontinyintPrecision of the parameter if numeric-based; otherwise, 0.
scaletinyintScale of the parameter if 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 default value.

 SQL Server only maintains default values for CLR objects in this catalog view; therefore, this column has 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_idintNon-zero if the data type of the parameter is xml and the XML is typed. The value is the ID of the collection containing the validating XML schema namespace of the parameter.

0 = No XML schema collection.
is_readonlybit1 = Parameter is READONLY; otherwise, 0.
is_nullablebit1 = Parameter is nullable. (the default).

0 = Parameter is not nullable, for more efficient execution of natively-compiled stored procedures.

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.all_parameters (Transact-SQL)
sys.system_parameters (Transact-SQL)

Community Additions

© 2016 Microsoft