28.3.30 The INFORMATION_SCHEMA ROUTINES Table (original) (raw)

28.3.30 The INFORMATION_SCHEMA ROUTINES Table

The ROUTINES table provides information about stored routines (stored procedures and stored functions). The ROUTINES table does not include built-in (native) functions or loadable functions.

The ROUTINES table has these columns:

• SPECIFIC_NAME
  The name of the routine.
• ROUTINE_CATALOG
  The name of the catalog to which the routine belongs. This value is always def.
• ROUTINE_SCHEMA
  The name of the schema (database) to which the routine belongs.
• ROUTINE_NAME
  The name of the routine.
• ROUTINE_TYPE
PROCEDURE for stored procedures,FUNCTION for stored functions.
• DATA_TYPE
  If the routine is a stored function, the return value data type. If the routine is a stored procedure, this value is empty.
  The DATA_TYPE value is the type name only with no other information. TheDTD_IDENTIFIER value contains the type name and possibly other information such as the precision or length.
• CHARACTER_MAXIMUM_LENGTH
  For stored function string return values, the maximum length in characters. If the routine is a stored procedure, this value is NULL.
• CHARACTER_OCTET_LENGTH
  For stored function string return values, the maximum length in bytes. If the routine is a stored procedure, this value isNULL.
• NUMERIC_PRECISION
  For stored function numeric return values, the numeric precision. If the routine is a stored procedure, this value isNULL.
• NUMERIC_SCALE
  For stored function numeric return values, the numeric scale. If the routine is a stored procedure, this value isNULL.
• DATETIME_PRECISION
  For stored function temporal return values, the fractional seconds precision. If the routine is a stored procedure, this value is NULL.
• CHARACTER_SET_NAME
  For stored function character string return values, the character set name. If the routine is a stored procedure, this value is NULL.
• COLLATION_NAME
  For stored function character string return values, the collation name. If the routine is a stored procedure, this value is NULL.
• DTD_IDENTIFIER
  If the routine is a stored function, the return value data type. If the routine is a stored procedure, this value is empty.
  The DATA_TYPE value is the type name only with no other information. TheDTD_IDENTIFIER value contains the type name and possibly other information such as the precision or length.
• ROUTINE_BODY
  The language used for the routine definition. This value is always SQL.
• ROUTINE_DEFINITION
  The text of the SQL statement executed by the routine.
• EXTERNAL_NAME
  This value is always NULL.
• EXTERNAL_LANGUAGE
  The language of the stored routine. The value is read from theexternal_language column of themysql.routines data dictionary table.
• PARAMETER_STYLE
  This value is always SQL.
• IS_DETERMINISTIC
YES or NO, depending on whether the routine is defined with theDETERMINISTIC characteristic.
• SQL_DATA_ACCESS
  The data access characteristic for the routine. The value is one of CONTAINS SQL, NO SQL, READS SQL DATA, orMODIFIES SQL DATA.
• SQL_PATH
  This value is always NULL.
• SECURITY_TYPE
  The routine SQL SECURITY characteristic. The value is one of DEFINER orINVOKER.
• CREATED
  The date and time when the routine was created. This is aTIMESTAMP 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 value is the same as the CREATED value.
• 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 7.1.11, “Server SQL Modes”.
• ROUTINE_COMMENT
  The text of the comment, if the routine has one. If not, this value is empty.
• DEFINER
  The account named in the DEFINER clause (often the user who created the routine), in'_`username`_'@'_`hostname`_' format.
• CHARACTER_SET_CLIENT
  The session value of thecharacter_set_client system variable when the routine was created.
• COLLATION_CONNECTION
  The session value of thecollation_connection system variable when the routine was created.
• DATABASE_COLLATION
  The collation of the database with which the routine is associated.

Notes

• To see information about a routine, you must be the user named as the routine DEFINER, have theSHOW_ROUTINE privilege, have the SELECT privilege at the global level, or have the CREATE ROUTINE, ALTER ROUTINE, or EXECUTE privilege granted at a scope that includes the routine. TheROUTINE_DEFINITION column isNULL if you have onlyCREATE ROUTINE,ALTER ROUTINE, orEXECUTE.
• Information about stored function return values is also available in the PARAMETERS table. The return value row for a stored function can be identified as the row that has anORDINAL_POSITION value of 0.