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

20.16 The INFORMATION_SCHEMA ROUTINES Table

The ROUTINES table provides information about stored routines (both procedures and functions). The ROUTINES table does not include 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.

INFORMATION_SCHEMA Namemysql.proc NameRemarks
SPECIFIC_NAMEspecific_name
ROUTINE_CATALOG NULL
ROUTINE_SCHEMAdb
ROUTINE_NAMEname
ROUTINE_TYPEtype{PROCEDURE|FUNCTION}
DTD_IDENTIFIER data type descriptor
ROUTINE_BODY SQL
ROUTINE_DEFINITIONbody or body_utf8see Notes
EXTERNAL_NAME NULL
EXTERNAL_LANGUAGElanguageNULL
PARAMETER_STYLE SQL
IS_DETERMINISTICis_deterministic
SQL_DATA_ACCESSsql_data_access
SQL_PATH NULL
SECURITY_TYPEsecurity_type
CREATEDcreated
LAST_ALTEREDmodified
SQL_MODEsql_modeMySQL extension
ROUTINE_COMMENTcommentMySQL extension
DEFINERdefinerMySQL extension
CHARACTER_SET_CLIENT MySQL extension
COLLATION_CONNECTION MySQL extension
DATABASE_COLLATION MySQL extension

Notes:

  • 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.

  • ROUTINE_DEFINITION is what is in mysql.proc.body_utf8 as of MySQL 5.1.21, mysql.proc.body before 5.1.21.

  • 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 column holds the same value as the CREATED column.

  • 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.7, “Server SQL Modes”.

  • CHARACTER_SET_CLIENT: The session value of the character_set_client system variable when the routine was created. This column was added in MySQL 5.1.21.

  • COLLATION_CONNECTION: The session value of the collation_connection system variable when the routine was created. This column was added in MySQL 5.1.21.

  • DATABASE_COLLATION: The collation of the database with which the routine is associated. This column was added in MySQL 5.1.21.


User Comments
  Posted by stephen d on February 8, 2007
In case you would want to view all the stored procedures in a Database then we can use :

SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE="PROCEDURE"
AND ROUTINE_SCHEMA="dbname";

You could use the same statement for FUNCTIONS just set
ROUTINE_TYPE="FUNCTION" in the WHERE clause
Sign Up Login You must be logged in to post a comment.