Listing Stored Procedures

 Listing Stored Procedures

Summary: in this tutorial, you will learn how to list stored procedures from databases in a MySQL Server.

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 the statement shows all characteristics of stored procedures including stored procedure names. It returns stored procedures that you have the privilege to access.

The following statement shows all stored procedures 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 procedures whose names contain the wordOrder:

SHOW PROCEDURE STATUS LIKE '%Order%'

Listing stored procedures using the data dictionary

The routines table in the information_schema the 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';

Showing stored procedures using MySQL Workbench

In MySQL Workbench, you can view all stored procedures from a database.

Step 1. Access the database that you want to view the stored procedures.

Step 2. Open the Stored Procedures menu. You will see a list of stored procedures that belong to the current database.

In this tutorial, you have learned how to list the stored procedures in a database by querying them from the data dictionary.