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]Code language: SQL (Structured Query Language) (sql)
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;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 procedure whose names contain the wordOrder:
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.