Share via


PARAMETERS (Transact-SQL)

Returns one row for each parameter of a user-defined function or stored procedure that can be accessed by the current user in the current database. For functions, this view also returns one row with return value information.

To retrieve information from these views, specify the fully qualified name of **INFORMATION_SCHEMA.**view_name.

Column name Data type Description

SPECIFIC_CATALOG

nvarchar(128)

Catalog name of the routine for which this is a parameter.

SPECIFIC_SCHEMA

nvarchar(128)

Name of the schema of the routine for which this is a parameter.

SPECIFIC_NAME

nvarchar(128)

Name of the routine for which this is a parameter.

ORDINAL_POSITION

int

Ordinal position of the parameter starting at 1. For the return value of a function, this is a 0.

PARAMETER_MODE

nvarchar(10)

Returns IN if an input parameter, OUT if an output parameter, and INOUT if an input/output parameter.

IS_RESULT

nvarchar(10)

Returns YES if indicates result of the routine that is a function. Otherwise, returns NO.

AS_LOCATOR

nvarchar(10)

Returns YES if declared as locator. Otherwise, returns NO.

PARAMETER_NAME

nvarchar(128)

Name of the parameter. NULL if this corresponds to the return value of a function.

DATA_TYPE

nvarchar(128)

System-supplied data type.

CHARACTER_MAXIMUM_LENGTH

int

Maximum length in characters for binary or character data types.

-1 for xml and large-value type data. Otherwise, returns NULL.

CHARACTER_OCTET_LENGTH

int

Maximum length, in bytes, for binary or character data types.

-1 for xml and large-value type data. Otherwise, returns NULL.

COLLATION_CATALOG

nvarchar(128)

Always returns NULL.

COLLATION_SCHEMA

nvarchar(128)

Always returns NULL.

COLLATION_NAME

nvarchar(128)

Name of the collation of the parameter. If not one of the character types, returns NULL.

CHARACTER_SET_CATALOG

nvarchar(128)

Catalog name of the character set of the parameter. If not one of the character types, returns NULL.

CHARACTER_SET_SCHEMA

nvarchar(128)

Always returns NULL.

CHARACTER_SET_NAME

nvarchar(128)

Name of the character set of the parameter. If not one of the character types, returns NULL.

NUMERIC_PRECISION

tinyint

Precision of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, returns NULL.

NUMERIC_PRECISION_RADIX

smallint

Precision radix of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, returns NULL.

NUMERIC_SCALE

tinyint

Scale of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, returns NULL.

DATETIME_PRECISION

smallint

Precision in fractional seconds if the parameter type is datetime or smalldatetime. Otherwise, returns NULL.

INTERVAL_TYPE

nvarchar(30)

NULL. Reserved for future use.

INTERVAL_PRECISION

smallint

NULL. Reserved for future use.

USER_DEFINED_TYPE_CATALOG

nvarchar(128)

NULL. Reserved for future use.

USER_DEFINED_TYPE_SCHEMA

nvarchar(128)

NULL. Reserved for future use.

USER_DEFINED_TYPE_NAME

nvarchar(128)

NULL. Reserved for future use.

SCOPE_CATALOG

nvarchar(128)

NULL. Reserved for future use.

SCOPE_SCHEMA

nvarchar(128)

NULL. Reserved for future use.

SCOPE_NAME

nvarchar(128)

NULL. Reserved for future use.

See Also

Reference

System Views (Transact-SQL)
Information Schema Views (Transact-SQL)
sys.columns (Transact-SQL)
sys.objects (Transact-SQL)
sys.parameters (Transact-SQL)

Other Resources

Querying the SQL Server System Catalog

Help and Information

Getting SQL Server 2005 Assistance