MySQL 5.0 Certification Study Guide

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;