Listing Stored Procedures in MySQL

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]Code language: SQL (Structured Query Language) (sql)

The SHOW PROCEDURE STATUS statement shows all characteristics of stored procedures including stored procedure names. It returns stored procedures that you have the privilege to access.

For example, the following statement shows all stored procedures in the current MySQL server:

SHOW PROCEDURE STATUS;Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

For example, this statement lists all stored procedures in the sample database classicmodels:

SHOW PROCEDURE STATUS WHERE db = 'classicmodels';Code language: SQL (Structured Query Language) (sql)

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%'Code language: SQL (Structured Query Language) (sql)

The following statement shows all stored procedures whose names contain the word Order:

SHOW PROCEDURE STATUS LIKE '%Order%'Code language: SQL (Structured Query Language) (sql)

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>';Code language: SQL (Structured Query Language) (sql)

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';Code language: SQL (Structured Query Language) (sql)

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.

Was this tutorial helpful?