Edit

Share via

sys.parameters (Transact-SQL)

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric SQL database in Microsoft Fabric

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 name Data type Description
object_id int ID of the object to which this parameter belongs.
name sysname Name 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_id int ID of the 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.

Value = -1 when the column data type is varchar(max), nvarchar(max), varbinary(max), or xml.
precision tinyint Precision of the parameter if numeric-based; otherwise, 0.
scale tinyint Scale of the parameter if 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 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_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 Non-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_readonly bit 1 = Parameter is READONLY; otherwise, 0.
is_nullable bit 1 = Parameter is nullable. (the default).

0 = Parameter is not nullable, for more efficient execution of natively-compiled stored procedures.
encryption_type int Applies to: SQL Server 2016 (13.x) and later, SQL Database.

Encryption type:

1 = Deterministic encryption

2 = Randomized encryption
encryption_type_desc nvarchar(64) Applies to: SQL Server 2016 (13.x) and later, SQL Database.

Encryption type description:

RANDOMIZED

DETERMINISTIC
encryption_algorithm_name sysname Applies to: SQL Server 2016 (13.x) and later, SQL Database.

Name of encryption algorithm.

Only AEAD_AES_256_CBC_HMAC_SHA_512 is supported.
column_encryption_key_id int Applies to: SQL Server 2016 (13.x) and later, SQL Database.

ID of the CEK.
column_encryption_key_database_name sysname Applies to: SQL Server 2016 (13.x) and later, SQL Database.

The name of the database where the column encryption key exists if different than the database of the column. Is NULL if the key exists in the same database as the column.

Permissions

The visibility of the metadata in catalog views is limited to securables that a user either owns, or on which the user was 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.all_parameters (Transact-SQL)
sys.system_parameters (Transact-SQL)