sys-all-parameters-transact-sql.md

title	description	author	ms.author	ms.date	ms.service	ms.subservice	ms.topic	f1_keywords	helpviewer_keywords	dev_langs	monikerRange
sys.all_parameters (Transact-SQL)	sys.all_parameters (Transact-SQL)	rwestMSFT	randolphwest	04/30/2021	sql	system-objects	reference	all_parameters_TSQL sys.all_parameters all_parameters sys.all_parameters_TSQL	sys.all_parameters catalog view	TSQL	>=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current||=fabric

sys.all_parameters (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw]

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. [!INCLUDEssNoVersion] only maintains default values for CLR objects in this catalog view; therefore, this column will always have a value of 0 for [!INCLUDEtsql] objects. To view the default value of a parameter in a [!INCLUDEtsql] 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: [!INCLUDEsssql16-md] and later, [!INCLUDEssSDS_md]. Encryption type: 1 = Deterministic encryption 2 = Randomized encryption
encryption_type_desc	nvarchar(64)	Applies to: [!INCLUDEsssql16-md] and later, [!INCLUDEssSDS_md]. Encryption type description: RANDOMIZED DETERMINISTIC
encryption_algorithm_name	sysname	Applies to: [!INCLUDEsssql16-md] and later, [!INCLUDEssSDS_md]. Name of encryption algorithm. Only AEAD_AES_256_CBC_HMAC_SHA_512 is supported.
column_encryption_key_id	int	Applies to: [!INCLUDEsssql16-md] and later, [!INCLUDEssSDS_md]. ID of the CEK.
column_encryption_key_database_name	sysname	Applies to: [!INCLUDEsssql16-md] and later, [!INCLUDEssazuresynapse-md]. 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

[!INCLUDEssCatViewPerm] 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)