ROUTINES

Transact-SQL Reference

Transact-SQL Reference

ROUTINES

Contains one row for each stored procedure and function accessible to the current user in the current database. The columns that describe the return value apply only to functions. For stored procedures, these columns will be NULL.

The INFORMATION_SCHEMA.ROUTINES view is based on the sysobjects and syscolumns system tables.

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

Note  The ROUTINE_DEFINITION column contains the source statements that created the function, stored procedure, or trigger. These source statements are likely to contain embedded carriage returns. If you are returning this column to an application that is displaying the results in a text format, the embedded carriage returns in the ROUTINE_DEFINITION results may affect the formatting of the overall result set. If you select the ROUTINE_DEFINITION column, you must adjust for the embedded carriage returns; for example, by returning the result set into a grid or returning ROUTINE_DEFINITION into its own text box.

Column name Data type Description
SPECIFIC_CATALOG nvarchar(128) Specific name of the catalog.

For SQL Server 2000, this name is the same as ROUTINE_CATALOG.

SPECIFIC_SCHEMA nvarchar(128) Specific name of the catalog.

For SQL Server 2000, this is the same as ROUTINE_SCHEMA.

SPECIFIC_NAME nvarchar(128) Specific name of the catalog.

For SQL Server 2000, this is the same as ROUTINE_NAME.

ROUTINE_CATALOG nvarchar(128) Catalog name of the function.
ROUTINE_SCHEMA nvarchar(128) Owner name of the function.
ROUTINE_NAME nvarchar(128) Name of the function.
ROUTINE_TYPE nvarchar(20) Returns PROCEDURE for stored procedures, and FUNCTION for functions.
MODULE_CATALOG nvarchar(128) NULL. Reserved for future use.
MODULE_SCHEMA nvarchar(128) NULL. Reserved for future use.
MODULE_NAME nvarchar(128) NULL. Reserved for future use.
UDT_CATALOG nvarchar(128) NULL. Reserved for future use.
UDT_SCHEMA nvarchar(128) NULL. Reserved for future use.
UDT_NAME nvarchar(128) NULL. Reserved for future use.
DATA_TYPE nvarchar(128) Data type of the return value of the function. Returns table if a table-valued function.
CHARACTER_MAXIMUM_LENGTH int Maximum length in characters, if the return type is a character type.
CHARACTER_OCTET_LENGTH int Maximum length in bytes, if the return type is a character type.
COLLATION_CATALOG nvarchar(128) Catalog portion of the collation name of the return value. For noncharacter types, returns NULL.
COLLATION_SCHEMA nvarchar(128) Schema portion of the collation name of the return value. For noncharacter types, returns NULL.
COLLATION_NAME nvarchar(128) Collation name of the return value. For noncharacter types, returns NULL.
CHARACTER_SET_CATALOG nvarchar(128) Catalog name of the return value's character set. For noncharacter types, returns NULL.
CHARACTER_SET_SCHEMA nvarchar(128) Schema name of the return value's character set. For noncharacter types, returns NULL.
CHARACTER_SET_NAME nvarchar(128) Name of the return value's character set. For noncharacter types, returns NULL.
NUMERIC_PRECISION smallint Numeric precision of the return value. For the nonnumeric types, returns NULL.
NUMERIC_PRECISION_RADIX smallint Numeric precision radix of the return value. For nonnumeric types, returns NULL.
NUMERIC_SCALE smallint Scale of the return value. For nonnumeric types, returns NULL.
DATETIME_PRECISION smallint Fractional precision of a second if return value is of type datetime. Otherwise, returns NULL.
INTERVAL_TYPE nvarchar(30) NULL. Reserved for future use.
INTERVAL_PRECISION smallint NULL. Reserved for future use.
TYPE_UDT_CATALOG nvarchar(128) NULL. Reserved for future use.
TYPE_UDT_SCHEMA nvarchar(128) NULL. Reserved for future use.
TYPE_UDT_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.
MAXIMUM_CARDINALITY bigint NULL. Reserved for future use.
DTD_IDENTIFIER nvarchar(128) NULL. Reserved for future use.
ROUTINE_BODY nvarchar(30) Returns SQL for a Transact-SQL function, and EXTERNAL for an externally written function.

In SQL Server 2000, functions will always be SQL.

ROUTINE_DEFINITION nvarchar(4000) Definition text of the function or stored procedure if the function or stored procedure is not encrypted. Otherwise, returns NULL.
EXTERNAL_NAME nvarchar(128) NULL. Reserved for future use.
EXTERNAL_LANGUAGE nvarchar(30) NULL. Reserved for future use.
PARAMETER_STYLE nvarchar(30) NULL. Reserved for future use.
IS_DETERMINISTIC nvarchar(10) Returns YES if the routine is deterministic.

Returns NO if the routine is nondeterministic.

Always returns NO for stored procedures.

SQL_DATA_ACCESS nvarchar(30) Returns one of the following four values:

NONE = The function does not contain SQL.
CONTAINS = The function possibly contains SQL.
READS = The function possibly reads SQL data.
MODIFIES = The function possibly modifies SQL data.

In SQL Server 2000, returns READS for all functions, and MODIFIES for all stored procedures.

IS_NULL_CALL nvarchar(10) Indicates if the routine will be called if any of its arguments are NULL.

In SQL Server 2000, always returns YES.

SQL_PATH nvarchar(128) NULL. Reserved for future use.
SCHEMA_LEVEL_ROUTINE nvarchar(10) Returns YES if schema-level function, or NO if not a schema-level function.

In SQL Server 2000, always returns YES.

MAX_DYNAMIC_RESULT_SETS smallint Maximum number of dynamic result sets returned by routine.

Returns 0 if functions, and TBD if stored procedures.

IS_USER_DEFINED_CAST nvarchar(10) Returns YES if user-defined cast function, and NO if not a user-defined cast function.

In SQL Server 2000, always returns NO.

IS_IMPLICITLY_INVOCABLE nvarchar(10) Returns YES if the routine is implicitly invocable, and NO if function is not implicitly invocable.

In SQL Server 2000, always returns NO.

CREATED datetime Time the routine was created.
LAST_ALTERED datetime The last time the function was modified.

See Also

syscolumns

sysobjects