mysql-list-procedures.sql

Listing stored procedures using SHOW PROCEDURE STATUS statement
Here is the basic syntax of the SHOW PROCEDURE STATUS statement:

 SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE search_condition]
The SHOW PROCEDURE STATUS statement shows all characteristic of stored procedures including stored procedure names. It returns stored procedures that you have a privilege to access.

The following statement shows all stored procedure in the current MySQL server:

 SHOW PROCEDURE STATUS;
Here is the partial output:


If you just want to show stored procedures in a particular database, you can use a WHERE clause in the  SHOW PROCEDURE STATUS as shown in the following statement:

 SHOW PROCEDURE STATUS WHERE search_condition;
For example, this statement lists all stored procedures in the sample database classicmodels:

 SHOW PROCEDURE STATUS WHERE db = 'classicmodels';
In case you want to find stored procedures whose names contain a specific word, you can use the LIKE clause as follows:

 SHOW PROCEDURE STATUS LIKE '%pattern%'
The following statement shows all stored procedure whose names contain the wordOrder:

 SHOW PROCEDURE STATUS LIKE '%Order%'

Listing stored procedures using the data dictionary
The routines table in the information_schema database contains all information on the stored procedures and stored functions of all databases in the current MySQL server.

To show all stored procedures of a particular database, you use the following query:

 SELECT 
    routine_name
FROM
    information_schema.routines
WHERE
    routine_type = 'PROCEDURE'
        AND routine_schema = '<database_name>';
For example, this statement lists all stored procedures in the classicmodels database:

 SELECT 
    routine_name
FROM
    information_schema.routines
WHERE
    routine_type = 'PROCEDURE'
        AND routine_schema = 'classicmodels';