Skip to content

sys-all-parameters-transact-sql.md

Latest commit

 

History

History
68 lines (60 loc) · 6.72 KB

sys-all-parameters-transact-sql.md

File metadata and controls

68 lines (60 loc) · 6.72 KB
title sys.all_parameters (Transact-SQL)
description sys.all_parameters shows the union of all parameters that belong to user-defined or system objects.
author rwestMSFT
ms.author randolphwest
ms.date 11/28/2025
ms.service sql
ms.subservice system-objects
ms.topic reference
ms.custom
ignite-2025
f1_keywords
all_parameters_TSQL
sys.all_parameters
all_parameters
sys.all_parameters_TSQL
helpviewer_keywords
sys.all_parameters catalog view
dev_langs
TSQL
monikerRange >=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric || =fabric-sqldb

sys.all_parameters (Transact-SQL)

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

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's numeric-based; otherwise, 0.
scale tinyint Scale of the parameter if it's 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.

[!INCLUDE ssNoVersion] only maintains default values for CLR objects in this catalog view. Therefore, this column always has a value of 0 for [!INCLUDE tsql] objects. To view the default value of a parameter in a [!INCLUDE tsql] 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 isn't 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 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's no XML schema collection, or the parameter isn't XML.
is_readonly bit 1 = Parameter is READONLY; otherwise, 0.
is_nullable bit 1 = Parameter is nullable. (the default).

0 = Parameter isn't nullable, for more efficient execution of natively compiled stored procedures.
encryption_type int Encryption type:

1 = Deterministic encryption
2 = Randomized encryption

Applies to: [!INCLUDE sssql16-md] and later versions, and [!INCLUDE ssSDS_md].
encryption_type_desc nvarchar(64) Encryption type description:

RANDOMIZED
DETERMINISTIC

Applies to: [!INCLUDE sssql16-md] and later versions, and [!INCLUDE ssSDS_md].
encryption_algorithm_name sysname Name of encryption algorithm.

Only AEAD_AES_256_CBC_HMAC_SHA_512 is supported.

Applies to: [!INCLUDE sssql16-md] and later versions, and [!INCLUDE ssSDS_md].
column_encryption_key_id int ID of the CEK.

Applies to: [!INCLUDE sssql16-md] and later versions, and [!INCLUDE ssSDS_md].
column_encryption_key_database_name sysname The name of the database where the column encryption key exists if different than the database of the column. NULL if the key exists in the same database as the column.

Applies to: [!INCLUDE sssql16-md] and later versions, and [!INCLUDE ssazuresynapse-md].
vector_dimensions int Indicates how many dimensions the vector has.

Applies to: [!INCLUDE sssql25-md] and later versions, and [!INCLUDE ssSDS_md]
vector_base_type tinyint Indicates the data type used to store vector dimensions values.

0 = 32-bit (single-precision) float
1 = 16-bit (half-precision) float 1

Applies to: [!INCLUDE sssql25-md] and later versions, and [!INCLUDE ssSDS_md]
vector_base_type_desc nvarchar(10) Contains the textual description of the data type used to store vector dimensions values.

Applies to: [!INCLUDE sssql25-md] and later versions, and [!INCLUDE ssSDS_md]

1 For more information, see Half-precision floating-point format.

Permissions

[!INCLUDE ssCatViewPerm] For more information, see Metadata visibility configuration.

Related content