Skip navigation links
MySQL 5.4 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   NULL
ROUTINE_SCHEMA db  
ROUTINE_NAME name  
ROUTINE_TYPE type {PROCEDURE|FUNCTION}
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.


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.