How to List All Stored Procedures in MySQL

1. List All Stored Procedures in MySQL
2. Use the SHOW PROCEDURE STATUS Statement to List All Stored Procedures in MySQL
3. Use the WHERE Clause to List All Stored Procedures Within a Specific Database
4. Use LIKE Operator to List All Stored Procedures Containing a Particular Pattern
5. Use Data Directory to List All Stored Procedures in MySQL
6. Use MySQL Workbench to List All Stored Procedures in MySQL

This tutorial shows three ways to list all stored procedures in MySQL. These include the SHOW PROCEDURE STATUS statement, data directory, and MySQL workbench (a visual tool).

List All Stored Procedures in MySQL

We will use the data directory, MySQL Workbench, and SHOW PROCEDURE STATUS statement to list all stored procedures in MySQL. All the queries given in this article can be executed in the Windows command line once you log in to the MySQL Server.

We will be using MySQL Workbench (a visual tool) for this article to execute the queries because it displays big data in a proper tabular form to understand easily.

Use the SHOW PROCEDURE STATUS Statement to List All Stored Procedures in MySQL

The basic syntax for the SHOW PROCEDURE STATUS statement is below.

SHOW PROCEDURE STATUS [LIKE 'yourPattern' | WHERE searchCondition]

The following command shows the stored procedure’s all characteristics, including database names. It also contains procedure names, the date on which it is created and modified, a description, etc.

It returns all the stored procedures in the current MySQL server that we have permission (privilege) to access.

SHOW PROCEDURE STATUS;

Output:

Use the WHERE Clause to List All Stored Procedures Within a Specific Database

We can use the WHERE clause with the SHOW PROCEDURE STATUS statement to get all the stored procedures within a particular database. See the following query as an example.

SHOW PROCEDURE STATUS WHERE db = 'test';

Output:

Use LIKE Operator to List All Stored Procedures Containing a Particular Pattern

We can search for all the stored procedures having a particular pattern in the procedure’s name. The wildcard characters are useful for writing a pattern.

The example query is given below.

SHOW PROCEDURE STATUS LIKE '%perform%'

Output:

Use Data Directory to List All Stored Procedures in MySQL

The other way to list all the stored procedures is by querying the routines table of the information_schema database. The routines table has all the details about the stored functions and procedures for all the databases on the current MySQL Server.

Here, we can list all the stored procedures for all databases.

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

Output:

We can use the following query to list all the stored procedures for a specific database using the routines table.

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

Use MySQL Workbench to List All Stored Procedures in MySQL

We can click on the Stored Procedures option to list all the stored procedures for each database individually.