Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 37.7Mb
PDF (A4) - 37.7Mb
PDF (RPM) - 33.8Mb
HTML Download (TGZ) - 8.4Mb
HTML Download (Zip) - 8.4Mb
HTML Download (RPM) - 7.3Mb
Man Pages (TGZ) - 130.0Kb
Man Pages (Zip) - 185.6Kb
Info (Gzip) - 3.3Mb
Info (Zip) - 3.3Mb


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

Pre-General Availability Draft: 2018-02-22

25.19 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).

INFORMATION_SCHEMA NameRemarks
SPECIFIC_NAME 
ROUTINE_CATALOGdef
ROUTINE_SCHEMA 
ROUTINE_NAME 
ROUTINE_TYPE{PROCEDURE|FUNCTION}
DATA_TYPEsame as for COLUMNS table
CHARACTER_MAXIMUM_LENGTHsame as for COLUMNS table
CHARACTER_OCTET_LENGTHsame as for COLUMNS table
NUMERIC_PRECISIONsame as for COLUMNS table
NUMERIC_SCALEsame as for COLUMNS table
DATETIME_PRECISIONsame as for COLUMNS table
CHARACTER_SET_NAMEsame as for COLUMNS table
COLLATION_NAMEsame as for COLUMNS table
DTD_IDENTIFIERdata type descriptor
ROUTINE_BODYSQL
ROUTINE_DEFINITION 
EXTERNAL_NAMENULL
EXTERNAL_LANGUAGENULL
PARAMETER_STYLESQL
IS_DETERMINISTIC 
SQL_DATA_ACCESS 
SQL_PATHNULL
SECURITY_TYPE 
CREATED 
LAST_ALTERED 
SQL_MODEMySQL extension
ROUTINE_COMMENTMySQL extension
DEFINERMySQL extension
CHARACTER_SET_CLIENTMySQL extension
COLLATION_CONNECTIONMySQL extension
DATABASE_COLLATIONMySQL 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.

  • CREATED: The date and time when the routine was created. This is a TIMESTAMP value.

  • LAST_ALTERED: The date and time when the routine was last modified. This is a TIMESTAMP value. If the routine has not been modified since its creation, this column holds the same value as the CREATED column.

  • SQL_MODE: The SQL mode in effect when the routine was created or altered, and under which the routine executes. For the permitted values, see Section 5.1.8, “Server SQL Modes”.

  • CHARACTER_SET_CLIENT: The session value of the character_set_client system variable when the routine was created.

  • COLLATION_CONNECTION: The session value of the collation_connection system variable when the routine was created.

  • DATABASE_COLLATION: The collation of the database with which the routine is associated.

  • The DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, DATETIME_PRECISION, 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.

  • 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
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.