Skip navigation links
MySQL 5.5 Reference Manual :: 19 INFORMATION_SCHEMA Tables :: 19.14 The INFORMATION_SCHEMA ROUTINES Table
19.15 The INFORMATION_SCHEMA VIEWS Table »
Section Navigation      [Toggle]

19.14. 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) at this time.

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 Name mysql.proc Name Remarks
SPECIFIC_NAME specific_name  
ROUTINE_CATALOG   def
ROUTINE_SCHEMA db  
ROUTINE_NAME name  
ROUTINE_TYPE type {PROCEDURE|FUNCTION}
DATA_TYPE   same as for COLUMNS table
CHARACTER_MAXIMUM_LENGTH   same as for COLUMNS table
CHARACTER_OCTET_LENGTH   same as for COLUMNS table
NUMERIC_PRECISION   same as for COLUMNS table
NUMERIC_SCALE   same as for COLUMNS table
CHARACTER_SET_NAME   same as for COLUMNS table
COLLATION_NAME   same as for COLUMNS table
DTD_IDENTIFIER   data type descriptor
ROUTINE_BODY   SQL
ROUTINE_DEFINITION body  
EXTERNAL_NAME   NULL
EXTERNAL_LANGUAGE language NULL
PARAMETER_STYLE   SQL
IS_DETERMINISTIC is_deterministic  
SQL_DATA_ACCESS sql_data_access  
SQL_PATH   NULL
SECURITY_TYPE security_type  
CREATED created  
LAST_ALTERED modified  
SQL_MODE sql_mode MySQL extension
ROUTINE_COMMENT comment MySQL extension
DEFINER definer MySQL 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.

  • CHARACTER_SET_CLIENT is the session value of the character_set_client system variable when the routine was created. COLLATION_CONNECTION is the session value of the collation_connection system variable when the routine was created. DATABASE_COLLATION is the collation of the database with which the routine is associated.

  • The DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, CHARACTER_SET_NAME, and COLLATION_NAME columns provide information about the data type for the RETURNS clause of stored functions. If a stored routine is a stored procedure, these columns all are NULL. These columns were added in MySQL 5.5.3.

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


User Comments

Posted by Stephen D on February 8 2007 12:55pm[Delete] [Edit]

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

Add your own comment.