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 tablesin addition to being ANSI standardhave 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 codesomething that is not practical with SHOW statement output.

7.4.1. 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

 

7.4.2. SHOW CREATE PROCEDURE/FUNCTION

The SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION statements return the CREATE statement necessary to re-create a particular stored program. Figure 7-12 shows the output of this version of SHOW. Note that we used the "View Field pop-up editor" right-click option to load the text output returned by this statement into a more readable Field Viewer window.

7.4.3. INFORMATION_SCHEMA.ROUTINES Table

The INFORMATION_SCHEMA.ROUTINES table returns a variety of information about stored procedures and functions. You can use the WHERE clause and column lists within the SELECT statement to format this output in various interesting ways.

Figure 7-12. SHOW CREATE FUNCTION

This table does not contain information about triggers, but you can retrieve trigger information from the INFORMATION_SCHEMA.TRIGGERS table described in the next section.

Figure 7-13 shows the structure of the INFORMATION_SCHEMA.ROUTINES table.

You can use INFORMATION_SCHEMA.ROUTINES to return any of the data returned by the SHOW PROCEDURE STATUS, SHOW FUNCTION STATUS, SHOW CREATE PROCEDURE, and SHOW CREATE FUNCTION statements. For instance, in Figure 7-14, we produce a report that includes both the procedure/function definitions and other information about these programs.

7.4.4. INFORMATION_SCHEMA.TRIGGERS Table

The INFORMATION_SCHEMA.TRIGGERS table contains details about all of the triggers that are defined on the MySQL server. Figure 7-15 shows the output from a query against this table (using the "View Field pop-up editor" right-click option to view the contents of the action_statement column).

Figure 7-13. Structure of the INFORMATION_SCHEMA.ROUTINES table