ROUTINES (Transact-SQL)

 

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

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.

System_CAPS_ICON_note.jpg Note


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 nameData typeDescription
SPECIFIC_CATALOGnvarchar(128)Specific name of the catalog. This name is the same as ROUTINE_CATALOG.
SPECIFIC_SCHEMAnvarchar(128)Specific name of the schema.

 ** Important ** Do not use INFORMATION_SCHEMA views to determine the schema of an object. The only reliable way to find the schema of a object is to query the sys.objects catalog view.
SPECIFIC_NAMEnvarchar(128)Specific name of the catalog. This name is the same as ROUTINE_NAME.
ROUTINE_CATALOGnvarchar(128)Catalog name of the function.
ROUTINE_SCHEMAnvarchar(128)Name of the schema that contains this function.

 ** Important ** Do not use INFORMATION_SCHEMA views to determine the schema of an object. The only reliable way to find the schema of a object is to query the sys.objects catalog view.
ROUTINE_NAMEnvarchar(128)Name of the function.
ROUTINE_TYPEnvarchar(20)Returns PROCEDURE for stored procedures, and FUNCTION for functions.
MODULE_CATALOGnvarchar(128)NULL. Reserved for future use.
MODULE_SCHEMAnvarchar(128)NULL. Reserved for future use.
MODULE_NAMEnvarchar(128)NULL. Reserved for future use.
UDT_CATALOGnvarchar(128)NULL. Reserved for future use.
UDT_SCHEMAnvarchar(128)NULL. Reserved for future use.
UDT_NAMEnvarchar(128)NULL. Reserved for future use.
DATA_TYPEnvarchar(128)Data type of the return value of the function. Returns table if a table-valued function.
CHARACTER_MAXIMUM_LENGTHintMaximum length in characters, if the return type is a character type.

-1 for xml and large-value type data.
CHARACTER_OCTET_LENGTHintMaximum length in bytes, if the return type is a character type.

-1 for xml and large-value type data.
COLLATION_CATALOGnvarchar(128)Always returns NULL.
COLLATION_SCHEMAnvarchar(128)Always returns NULL.
COLLATION_NAMEnvarchar(128)Collation name of the return value. For noncharacter types, returns NULL.
CHARACTER_SET_CATALOGnvarchar(128)Always returns NULL.
CHARACTER_SET_SCHEMAnvarchar(128)Always returns NULL.
CHARACTER_SET_NAMEnvarchar(128)Name of the character set of the return value. For noncharacter types, returns NULL.
NUMERIC_PRECISIONsmallintNumeric precision of the return value. For the nonnumeric types, returns NULL.
NUMERIC_PRECISION_RADIXsmallintNumeric precision radix of the return value. For nonnumeric types, returns NULL.
NUMERIC_SCALEsmallintScale of the return value. For nonnumeric types, returns NULL.
DATETIME_PRECISIONsmallintFractional precision of a second if the return value is of type datetime. Otherwise, returns NULL.
INTERVAL_TYPEnvarchar(30)NULL. Reserved for future use.
INTERVAL_PRECISIONsmallintNULL. Reserved for future use.
TYPE_UDT_CATALOGnvarchar(128)NULL. Reserved for future use.
TYPE_UDT_SCHEMAnvarchar(128)NULL. Reserved for future use.
TYPE_UDT_NAMEnvarchar(128)NULL. Reserved for future use.
SCOPE_CATALOGnvarchar(128)NULL. Reserved for future use.
SCOPE_SCHEMAnvarchar(128)NULL. Reserved for future use.
SCOPE_NAMEnvarchar(128)NULL. Reserved for future use.
MAXIMUM_CARDINALITYbigintNULL. Reserved for future use.
DTD_IDENTIFIERnvarchar(128)NULL. Reserved for future use.
ROUTINE_BODYnvarchar(30)Returns SQL for a Transact-SQL function and EXTERNAL for an externally written function.

Functions will always be SQL.
ROUTINE_DEFINITIONnvarchar(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 that you obtain the complete definition, query the OBJECT_DEFINITION function or the definition column in the sys.sql_modules catalog view.
EXTERNAL_NAMEnvarchar(128)NULL. Reserved for future use.
EXTERNAL_LANGUAGEnvarchar(30)NULL. Reserved for future use.
PARAMETER_STYLEnvarchar(30)NULL. Reserved for future use.
IS_DETERMINISTICnvarchar(10)Returns YES if the routine is deterministic.

Returns NO if the routine is nondeterministic.

Always returns NO for stored procedures.
SQL_DATA_ACCESSnvarchar(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_CALLnvarchar(10)Indicates whether the routine will be called if any one of its arguments is NULL.
SQL_PATHnvarchar(128)NULL. Reserved for future use.
SCHEMA_LEVEL_ROUTINEnvarchar(10)Returns YES if schema-level function, or NO if not a schema-level function.

Always returns YES.
MAX_DYNAMIC_RESULT_SETSsmallintMaximum number of dynamic result sets returned by routine.

Returns 0 if functions.
IS_USER_DEFINED_CASTnvarchar(10)Returns YES if user-defined cast function, and NO if not a user-defined cast function.

Always returns NO.
IS_IMPLICITLY_INVOCABLEnvarchar(10)Returns YES if the routine can be implicitly invoked, and NO if function cannot be implicitly invoked.

Always returns NO.
CREATEDdatetimeTime when the routine was created.
LAST_ALTEREDdatetimeThe last time the function was modified.

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

Community Additions

ADD
Show: