Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 31.2Mb
PDF (A4) - 31.2Mb
PDF (RPM) - 29.4Mb
HTML Download (TGZ) - 7.3Mb
HTML Download (Zip) - 7.3Mb
HTML Download (RPM) - 6.2Mb
Man Pages (TGZ) - 179.0Kb
Man Pages (Zip) - 289.5Kb
Info (Gzip) - 3.0Mb
Info (Zip) - 3.0Mb
Excerpts from this Manual

MySQL 5.6 Reference Manual  /  INFORMATION_SCHEMA Tables  /  The INFORMATION_SCHEMA ROUTINES Table

21.19 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 column named mysql.proc Name indicates the mysql.proc table column that corresponds to the INFORMATION_SCHEMA ROUTINES table column, if any.

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. MySQL calculates EXTERNAL_LANGUAGE thus:

    • If mysql.proc.language='SQL', EXTERNAL_LANGUAGE is NULL

    • Otherwise, EXTERNAL_LANGUAGE is what is in mysql.proc.language. However, we do not have external languages yet, so it is always NULL.

  • 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 Section 5.1.10, “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.