28.3.30 The INFORMATION_SCHEMA ROUTINES Table

Skip to Main Content

MySQL 8.4 Reference Manual Including MySQL NDB Cluster 8.4

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. The DTD_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 is NULL. - NUMERIC_PRECISION

For stored function numeric return values, the numeric precision. If the routine is a stored procedure, this value is NULL. - NUMERIC_SCALE

For stored function numeric return values, the numeric scale. If the routine is a stored procedure, this value is NULL. - 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. The DTD_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 the external_language column of the mysql.routines data dictionary table. - PARAMETER_STYLE

This value is always SQL. - IS_DETERMINISTIC

YES or NO, depending on whether the routine is defined with the DETERMINISTIC characteristic. - SQL_DATA_ACCESS

The data access characteristic for the routine. The value is one of CONTAINS SQL, NO SQL, READS SQL DATA, or MODIFIES SQL DATA. - SQL_PATH

This value is always NULL. - SECURITY_TYPE

The routine SQL SECURITY characteristic. The value is one of DEFINER or INVOKER. - 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 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 'user_name'@'host_name' format. - 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.

Notes

  • To see information about a routine, you must be the user named as the routine DEFINER, have the SHOW_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. The ROUTINE_DEFINITION column is NULL if you have only CREATE ROUTINE, ALTER ROUTINE, or EXECUTE.
  • 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 an ORDINAL_POSITION value of 0.