sys.all_parameters (Transact-SQL)
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. |
Permissions
In SQL Server 2005 and later versions, 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.