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