The Information Schema ROUTINES table stores information about stored procedures and stored functions, including their definitions and properties.
The Information Schema ROUTINES table stores information about stored procedures and stored functions.
It contains the following columns:
SPECIFIC_NAME
ROUTINE_CATALOG
Always def.
ROUTINE_SCHEMA
Database name associated with the routine.
ROUTINE_NAME
Name of the routine.
ROUTINE_TYPE
Whether the routine is a PROCEDURE or a FUNCTION.
DATA_TYPE
The return value's data type (for stored functions).
CHARACTER_MAXIMUM_LENGTH
Maximum length.
CHARACTER_OCTET_LENGTH
Same as the CHARACTER_MAXIMUM_LENGTH except for multi-byte character sets.
NUMERIC_PRECISION
For numeric types, the precision (number of significant digits) for the column. NULL if not a numeric field.
NUMERIC_SCALE
For numeric types, the scale (significant digits to the right of the decimal point). NULL if not a numeric field.
DATETIME_PRECISION
Fractional-seconds precision, or NULL if not a time data type.
CHARACTER_SET_NAME
Character set if a non-binary string data type, otherwise NULL.
COLLATION_NAME
Collation if a non-binary string data type, otherwise NULL.
DATA_TYPE
The column's data type.
ROUTINE_BODY
Always SQL.
ROUTINE_DEFINITION
Definition of the routine.
EXTERNAL_NAME
Always NULL.
EXTERNAL_LANGUAGE
Always SQL.
PARAMETER_STYLE
Always SQL.
IS_DETERMINISTIC
Whether the routine is deterministic (can produce only one result for a given list of parameters) or not.
SQL_DATA_ACCESS
One of READS SQL DATA, MODIFIES SQL DATA, CONTAINS SQL, or NO SQL.
SQL_PATH
Always NULL.
SECURITY_TYPE
INVOKER or DEFINER. Indicates which user's privileges apply to this routine.
CREATED
Date and time the routine was created.
LAST_ALTERED
Date and time the routine was last changed.
SQL_MODE
The SQL_MODE at the time the routine was created.
ROUTINE_COMMENT
Comment associated with the routine.
DEFINER
If the SECURITY_TYPE is DEFINER, this value indicates which user defined this routine.
CHARACTER_SET_CLIENT
The character set used by the client that created the routine.
COLLATION_CONNECTION
The collation (and character set) used by the connection that created the routine.
DATABASE_COLLATION
The default collation (and character set) for the database, at the time the routine was created.
It provides information similar to, but more complete, than the SHOW PROCEDURE STATUS and SHOW FUNCTION STATUS statements.
For information about the parameters accepted by the routine, you can query the information_schema.PARAMETERS table.
This page is licensed: CC BY-SA / Gnu FDL
Last updated
Was this helpful?
Was this helpful?
