# Information Schema ROUTINES Table

The [Information Schema](https://mariadb.com/docs/server/reference/system-tables/information-schema) `ROUTINES` table stores information about [stored procedures](https://mariadb.com/docs/server/server-usage/stored-routines/stored-procedures) and [stored functions](https://mariadb.com/docs/server/server-usage/stored-routines/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](https://mariadb.com/docs/server/reference/data-types) (for stored functions).                                                                                                                       |
| CHARACTER\_MAXIMUM\_LENGTH | Maximum length.                                                                                                                                                                                                                    |
| CHARACTER\_OCTET\_LENGTH   | Same as the CHARACTER\_MAXIMUM\_LENGTH except for multi-byte [character sets](https://mariadb.com/docs/server/reference/data-types/string-data-types/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](https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types).                                                                                    |
| CHARACTER\_SET\_NAME       | [Character set](https://mariadb.com/docs/server/reference/data-types/string-data-types/character-sets) if a non-binary [string data type](https://mariadb.com/docs/server/reference/data-types/string-data-types), otherwise NULL. |
| COLLATION\_NAME            | [Collation](https://mariadb.com/docs/server/reference/data-types/string-data-types/character-sets) if a non-binary [string data type](https://mariadb.com/docs/server/reference/data-types/string-data-types), otherwise NULL.     |
| DATA\_TYPE                 | The column's [data type](https://mariadb.com/docs/server/reference/data-types).                                                                                                                                                    |
| 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](https://mariadb.com/docs/server/server-management/variables-and-modes/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](https://mariadb.com/docs/server/reference/data-types/string-data-types/character-sets) used by the client that created the routine.                                                                            |
| COLLATION\_CONNECTION      | The [collation](https://mariadb.com/docs/server/reference/data-types/string-data-types/character-sets) (and character set) used by the connection that created the routine.                                                        |
| DATABASE\_COLLATION        | The default [collation](https://mariadb.com/docs/server/reference/data-types/string-data-types/character-sets) (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](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-procedure-status) and [SHOW FUNCTION STATUS](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-function-status) statements.

For information about the parameters accepted by the routine, you can query the [information\_schema.PARAMETERS](https://mariadb.com/docs/server/reference/system-tables/information-schema/information-schema-tables/information-schema-parameters-table) table.

## See also

* [Stored Function Overview](https://mariadb.com/docs/server/server-usage/stored-routines/stored-functions/stored-function-overview)
* [Stored Procedure Overview](https://mariadb.com/docs/server/server-usage/stored-routines/stored-procedures/stored-procedure-overview)

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}