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

19.8 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 
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

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.


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.