25.23 The INFORMATION_SCHEMA ROUTINES Table

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 5.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 of 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.

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.