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.
Was this tutorial helpful?