W3cubDocs

Information Schema ROUTINES Table

The Information Schema ROUTINES table stores information about stored procedures and stored functions.

It contains the following columns:

Column	Description
`SPECIFIC_NAME`
`ROUTINE_CATALOG`	Always `def`.
`ROUTINE_SCHEMA`	Database name associated with the routine.
`ROUTINE_NAME`	Name of the routine.
`ROUTINE_TYPE`	Whether the routine is a `PROCEDURE` or a `FUNCTION`.
`DATA_TYPE`	The return value's data type (for stored functions).
`CHARACTER_MAXIMUM_LENGTH`	Maximum length.
`CHARACTER_OCTET_LENGTH`	Same as the `CHARACTER_MAXIMUM_LENGTH` except for multi-byte character sets.
`NUMERIC_PRECISION`	For numeric types, the precision (number of significant digits) for the column. `NULL` if not a numeric field.
`NUMERIC_SCALE`	For numeric types, the scale (significant digits to the right of the decimal point). NULL if not a numeric field.
`DATETIME_PRECISION`	Fractional-seconds precision, or NULL if not a time data type.
`CHARACTER_SET_NAME`	Character set if a non-binary string data type, otherwise `NULL`.
`COLLATION_NAME`	Collation if a non-binary string data type, otherwise NULL.
`DATA_TYPE`	The column's data type.
`ROUTINE_BODY`	Always `SQL`.
`ROUTINE_DEFINITION`	Definition of the routine.
`EXTERNAL_NAME`	Always `NULL`.
`EXTERNAL_LANGUAGE`	Always `SQL`.
`PARAMETER_STYLE`	Always `SQL`.
`IS_DETERMINISTIC`	Whether the routine is deterministic (can produce only one result for a given list of parameters) or not.
`SQL_DATA_ACCESS`	One of `READS SQL DATA`, `MODIFIES SQL DATA`, `CONTAINS SQL`, or `NO SQL`.
`SQL_PATH`	Always `NULL`.
`SECURITY_TYPE`	`INVOKER` or `DEFINER`. Indicates which user's privileges apply to this routine.
`CREATED`	Date and time the routine was created.
`LAST_ALTERED`	Date and time the routine was last changed.
`SQL_MODE`	The `SQL_MODE` at the time the routine was created.
`ROUTINE_COMMENT`	Comment associated with the routine.
`DEFINER`	If the `SECURITY_TYPE` is `DEFINER`, this value indicates which user defined this routine.
`CHARACTER_SET_CLIENT`	The character set used by the client that created the routine.
`COLLATION_CONNECTION`	The collation (and character set) used by the connection that created the routine.
`DATABASE_COLLATION`	The default collation (and character set) for the database, at the time the routine was created.

It provides information similar to, but more complete, than the SHOW PROCEDURE STATUS and SHOW FUNCTION STATUS statements.

For information about the parameters accepted by the routine, you can query the information_schema.PARAMETERS table.

Information Schema ROUTINES Table

See also