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. .gif "Important note") Important
Do not use INFORMATION_SCHEMA views to determine the schema of an object. The only reliable way to find the schema of an object is to query the sys.objects catalog view or use the OBJECT_SCHEMA_NAME function.
|
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. |
Examples
The following example returns information about each parameter specified in a user-defined function or stored procedure.
USE AdventureWorks;
GO
SELECT SPECIFIC_SCHEMA, SPECIFIC_NAME, PARAMETER_NAME, PARAMETER_MODE, ORDINAL_POSITION, DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION, NUMERIC_SCALE
FROM INFORMATION_SCHEMA.PARAMETERS;