sys.parameters (Transact-SQL)

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

Is 0 if there is no XML schema collection.

See Also

Reference

Object Catalog Views (Transact-SQL)
Catalog Views (Transact-SQL)
sys.all_parameters (Transact-SQL)
sys.system_parameters (Transact-SQL)

Other Resources

Querying the SQL Server System Catalog FAQ
Implementing User-defined Types

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

14 April 2006

New content:
  • In the description for user_type_id, added information about how to return the name of the type.
Updated content:
  • Corrected the description of has_default_value.