ROUTINES

The ROUTINES view contains information about stored routines (procedures and functions). It doesn’t include built-in (native) or loadable functions.

Column	Description
SPECIFIC_NAME	The name of the routine.
ROUTINE_CATALOG	The catalog to which the routine belongs. This value is always def.
ROUTINE_SCHEMA	The schema (database) to which the routine belongs.
ROUTINE_NAME	The name of the routine.
ROUTINE_TYPE	Type of the routine: PROCEDURE for stored procedures, FUNCTION for stored functions.
DATA_TYPE	The return value data type for functions. Empty for procedures. It contains the type name only, without other details like precision.
CHARACTER_MAXIMUM_LENGTH	Maximum length in characters for string return values (functions only). NULL for procedures.
CHARACTER_OCTET_LENGTH	Maximum length in bytes for string return values (functions only). NULL for procedures.
NUMERIC_PRECISION	Numeric precision for numeric return values (functions only). NULL for procedures.
NUMERIC_SCALE	Numeric scale for numeric return values (functions only). NULL for procedures.
DATETIME_PRECISION	Fractional seconds precision for temporal return values (functions only). NULL for procedures.
CHARACTER_SET_NAME	The character set for string return values (functions only). NULL for procedures.
COLLATION_NAME	The collation for string return values (functions only). NULL for procedures.
DTD_IDENTIFIER	Detailed data type for return values (functions only). Empty for procedures. It includes the type and other details, such as precision or length.
ROUTINE_BODY	The language used for the routine’s definition. Always SQL.
ROUTINE_DEFINITION	The SQL statement executed by the routine.
EXTERNAL_NAME	Always NULL.
EXTERNAL_LANGUAGE	The language of the stored routine.
PARAMETER_STYLE	Always SQL.
IS_DETERMINISTIC	Indicates whether the routine is DETERMINISTIC: YES if it is, NO if not.
SQL_DATA_ACCESS	The data access level: CONTAINS SQL, NO SQL, READS SQL DATA, or MODIFIES SQL DATA.
SQL_PATH	Always NULL.
SECURITY_TYPE	The security type: DEFINER or INVOKER.
CREATED	The TIMESTAMP when the routine was created.
LAST_ALTERED	The TIMESTAMP when the routine was last modified.
SQL_MODE	The SQL mode in effect when the routine was created or altered.
ROUTINE_COMMENT	The comment for the routine, if any.
DEFINER	The account named in the DEFINER clause, typically the creator of the routine, in 'user_name'@'host_name' format.
CHARACTER_SET_CLIENT	The session value of character_set_client when the routine was created.
COLLATION_CONNECTION	The session value of collation_connection when the routine was created.
DATABASE_COLLATION	The collation of the database associated with the routine.

Note

To access routine information, you must satisfy at least one of the following conditions:

• Be the user specified as the DEFINER of the routine.
• Have the SHOW_ROUTINE privilege.
• Have the CREATE ROUTINE, ALTER ROUTINE, or EXECUTE privilege on the routine.

If you have only CREATE ROUTINE, ALTER ROUTINE, or EXECUTE, and none of the first two conditions apply, the ROUTINE_DEFINITION column returns NULL.

For more information, see the MySQL documentation: The INFORMATION_SCHEMA ROUTINES Table.