MySQL Information Schema  /  The INFORMATION_SCHEMA ROUTINES Table

Chapter 15 The INFORMATION_SCHEMA ROUTINES Table

The ROUTINES table provides information about stored routines (stored procedures and stored functions). The ROUTINES table does not include built-in SQL functions or user-defined functions (UDFs).

The ROUTINES table has these columns:

  • SPECIFIC_NAME

    The name of the routine.

  • ROUTINE_CATALOG

    The name of the catalog to which the routine belongs. This value is always def.

  • ROUTINE_SCHEMA

    The name of the schema (database) to which the routine belongs.

  • ROUTINE_NAME

    The name of the routine.

  • ROUTINE_TYPE

    PROCEDURE for stored procedures, FUNCTION for stored functions.

  • DATA_TYPE

    If the routine is a stored function, the return value data type. If the routine is a stored procedure, this value is empty.

    The DATA_TYPE value is the type name only with no other information. The DTD_IDENTIFIER value contains the type name and possibly other information such as the precision or length.

  • CHARACTER_MAXIMUM_LENGTH

    For stored function string return values, the maximum length in characters. If the routine is a stored procedure, this value is NULL.

  • CHARACTER_OCTET_LENGTH

    For stored function string return values, the maximum length in bytes. If the routine is a stored procedure, this value is NULL.

  • NUMERIC_PRECISION

    For stored function numeric return values, the numeric precision. If the routine is a stored procedure, this value is NULL.

  • NUMERIC_SCALE

    For stored function numeric return values, the numeric scale. If the routine is a stored procedure, this value is NULL.

  • DATETIME_PRECISION

    For stored function temporal return values, the fractional seconds precision. If the routine is a stored procedure, this value is NULL.

  • CHARACTER_SET_NAME

    For stored function character string return values, the character set name. If the routine is a stored procedure, this value is NULL.

  • COLLATION_NAME

    For stored function character string return values, the collation name. If the routine is a stored procedure, this value is NULL.

  • DTD_IDENTIFIER

    If the routine is a stored function, the return value data type. If the routine is a stored procedure, this value is empty.

    The DATA_TYPE value is the type name only with no other information. The DTD_IDENTIFIER value contains the type name and possibly other information such as the precision or length.

  • ROUTINE_BODY

    The language used for the routine definition. This value is always SQL.

  • ROUTINE_DEFINITION

    The text of the SQL statement executed by the routine.

  • EXTERNAL_NAME

    This value is always NULL.

  • EXTERNAL_LANGUAGE

    The language of the stored routine. The value is read from the external_language column of the mysql.routines data dictionary table.

  • PARAMETER_STYLE

    This value is always SQL.

  • IS_DETERMINISTIC

    YES or NO, depending on whether the routine is defined with the DETERMINISTIC characteristic.

  • SQL_DATA_ACCESS

    The data access characteristic for the routine. The value is one of CONTAINS SQL, NO SQL, READS SQL DATA, or MODIFIES SQL DATA.

  • SQL_PATH

    This value is always NULL.

  • SECURITY_TYPE

    The routine SQL SECURITY characteristic. The value is one of DEFINER or INVOKER.

  • CREATED

    The date and time when the routine was created. This is a TIMESTAMP value.

  • LAST_ALTERED

    The date and time when the routine was last modified. This is a TIMESTAMP value. If the routine has not been modified since its creation, this value is the same as the CREATED value.

  • SQL_MODE

    The SQL mode in effect when the routine was created or altered, and under which the routine executes. For the permitted values, see Server SQL Modes.

  • ROUTINE_COMMENT

    The text of the comment, if the routine has one. If not, this value is empty.

  • DEFINER

    The account of the user who created the routine, in 'user_name'@'host_name' format.

  • CHARACTER_SET_CLIENT

    The session value of the character_set_client system variable when the routine was created.

  • COLLATION_CONNECTION

    The session value of the collation_connection system variable when the routine was created.

  • DATABASE_COLLATION

    The collation of the database with which the routine is associated.

Notes

  • Information about stored function return values is also available in the PARAMETERS table. The return value row for a stored function can be identified as the row that has an ORDINAL_POSITION value of 0.