18.9. Obtaining Stored Routine Metadata

The INFORMATION_SCHEMA database has a ROUTINES table that contains information about stored routines. For example, to display information about the world_record_count() procedure defined in Section 18.4, "Defining Stored Routines," use this statement:

 mysql> SELECT * FROM INFORMATION_SCHEMA.ROUTINES     -> WHERE ROUTINE_NAME = 'world_record_count'     -> AND ROUTINE_SCHEMA = 'world'\G *************************** 1. row ***************************      SPECIFIC_NAME: world_record_count    ROUTINE_CATALOG: NULL     ROUTINE_SCHEMA: world       ROUTINE_NAME: world_record_count       ROUTINE_TYPE: PROCEDURE     DTD_IDENTIFIER: NULL       ROUTINE_BODY: SQL ROUTINE_DEFINITION: BEGIN     SELECT 'Country', COUNT(*) FROM Country;     SELECT 'City', COUNT(*) FROM City;     SELECT 'CountryLanguage', COUNT(*) FROM CountryLanguage; END      EXTERNAL_NAME: NULL  EXTERNAL_LANGUAGE: NULL    PARAMETER_STYLE: SQL   IS_DETERMINISTIC: NO    SQL_DATA_ACCESS: CONTAINS SQL           SQL_PATH: NULL      SECURITY_TYPE: DEFINER            CREATED: 2005-02-24 08:49:36       LAST_ALTERED: 2005-02-24 08:49:36           SQL_MODE:     ROUTINE_COMMENT:             DEFINER: wuser@localhost 

For further information about INFORMATION_SCHEMA, see Chapter 20, "Obtaining Database Metadata."

MySQL also supports a family of SHOW statements that display metadata. Some of these display stored routine information:

• SHOW PROCEDURE STATUS and SHOW FUNCTION STATUS display some of the same information that is available in the ROUTINES table. These statements include a LIKE 'pattern' clause. If it is present, the statements display information about the routines that have a name that matches the pattern.

   SHOW PROCEDURE STATUS LIKE 'w%'; SHOW FUNCTION STATUS; 

  
  

• To display the definition of an individual stored procedure or function, use the SHOW CREATE PROCEDURE or SHOW CREATE FUNCTION statement.

   SHOW CREATE PROCEDURE world_record_count; SHOW CREATE FUNCTION add3;