Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

ROUTINES (Transact-SQL)

Updated: 15 September 2007

Returns one row for each stored procedure and function that can be accessed by 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.

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

ms188757.note(en-US,SQL.90).gifNote:
The ROUTINE_DEFINITION column contains the source statements that created the function or stored procedure. These source statements are likely to contain embedded carriage returns. If you are returning this column to an application that displays 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. This name is the same as ROUTINE_CATALOG.

SPECIFIC_SCHEMA

nvarchar(128)

Specific name of the schema.

SPECIFIC_NAME

nvarchar(128)

Specific name of the catalog. This name is the same as ROUTINE_NAME.

ROUTINE_CATALOG

nvarchar(128)

Catalog name of the function.

ROUTINE_SCHEMA

nvarchar(128)

Name of the schema that contains this 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.

-1 for xml and large-value type data.

CHARACTER_OCTET_LENGTH

int

Maximum length in bytes, if the return type is a character type.

-1 for xml and large-value type data.

COLLATION_CATALOG

nvarchar(128)

Always returns NULL.

COLLATION_SCHEMA

nvarchar(128)

Always returns NULL.

COLLATION_NAME

nvarchar(128)

Collation name of the return value. For noncharacter types, returns NULL.

CHARACTER_SET_CATALOG

nvarchar(128)

Always returns NULL.

CHARACTER_SET_SCHEMA

nvarchar(128)

Always returns NULL.

CHARACTER_SET_NAME

nvarchar(128)

Name of the character set of the return value. 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 the 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.

Functions will always be SQL.

ROUTINE_DEFINITION

nvarchar(4000)

Returns the first 4000 characters of the definition text of the function or stored procedure if the function or stored procedure is not encrypted. Otherwise, returns NULL.

To ensure you obtain the complete definition, query the OBJECT_DEFINITION function or the definition column in the sys.sql_modules catalog view.

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 values:

NONE = Function does not contain SQL.

CONTAINS = Function possibly contains SQL.

READS = Function possibly reads SQL data.

MODIFIES = Function possibly modifies SQL data.

Returns READS for all functions, and MODIFIES for all stored procedures.

IS_NULL_CALL

nvarchar(10)

Indicates whether the routine will be called if any one of its arguments is NULL.

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.

Always returns YES.

MAX_DYNAMIC_RESULT_SETS

smallint

Maximum number of dynamic result sets returned by routine.

Returns 0 if functions.

IS_USER_DEFINED_CAST

nvarchar(10)

Returns YES if user-defined cast function, and NO if not a user-defined cast function.

Always returns NO.

IS_IMPLICITLY_INVOCABLE

nvarchar(10)

Returns YES if the routine can be implicitly invoked, and NO if function cannot be implicitly invoked.

Always returns NO.

CREATED

datetime

Time when the routine was created.

LAST_ALTERED

datetime

The last time the function was modified.

Release History

15 September 2007

Changed content:
  • Corrected the definition of the ROUTINE_DEFINITION column.
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.