MySQL Stored Procedure Programming

Getting Information About Stored Programs

This section describes ways you can retrieve information about the stored programs that exist in your database.

In releases of MySQL prior to 5.0, extracting information about objects in the database was achieved by issuing SHOW statements. MySQL has extended the SHOW statement in version 5 to include information about stored programs.

However, in 5.0, MySQL also introduced the INFORMATION_SCHEMA database, which contains various tables that provide information about the objects that exist within the server. These tables are typically referred to as the data dictionary or as server metadata.

If you are a long-time user of the MySQL server, then using SHOW statements may seem a more natural approach to obtaining information about stored programs. However, the INFORMATION_SCHEMA tables—in addition to being ANSI standard—have the advantage of being amenable to various handy SELECT operations, such as grouping, counting, joining, and advanced filtering operations. You can also use INFORMATION_SCHEMA tables within your stored program code—something that is not practical with SHOW statement output.

SHOW PROCEDURE/FUNCTION STATUS

The SHOW PROCEDURE STATUS and SHOW FUNCTION STATUS statements return information about the stored programs within the server. The syntax of this form of the SHOW statement is:

    SHOW {PROCEDURE|FUNCTION} STATUS [LIKE pattern]

Figure 7-11 provides an example of SHOW PROCEDURE status output.

Figure 7-11. SHOW PROCEDURE STATUS

SHOW ...