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_LANGUAGEthus:If
mysql.proc.language='SQL',EXTERNAL_LANGUAGEisNULLOtherwise,
EXTERNAL_LANGUAGEis what is inmysql.proc.language. However, we do not have external languages yet, so it is alwaysNULL.
CHARACTER_SET_CLIENTis the session value of thecharacter_set_clientsystem variable when the routine was created.COLLATION_CONNECTIONis the session value of thecollation_connectionsystem variable when the routine was created.DATABASE_COLLATIONis 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, andCOLLATION_NAMEcolumns provide information about the data type for theRETURNSclause of stored functions. If a stored routine is a stored procedure, these columns all areNULL. These columns were added in MySQL 5.5.3.Information about stored function
RETURNSdata types is also available in thePARAMETERStable. The return value data type row for a function can be identified as the row that has anORDINAL_POSITIONvalue of 0.