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