20.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 Namemysql.proc NameRemarks
SPECIFIC_NAMEspecific_name 
ROUTINE_CATALOG def
ROUTINE_SCHEMAdb 
ROUTINE_NAMEname 
ROUTINE_TYPEtype{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_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
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.

Copyright © 2010-2020 Platon Technologies, s.r.o.           Home | Man pages | tLDP | Documents | Utilities | About
Design by styleshout