In this tutorial, we will learn how to list stored procedures in the MySQL database. It is a super simple tutorial and we will make it more effective by using multiple examples. There are two ways you can list the procedures and functions in MySQL. We will see both ways through examples and proper snapshots. Here we go!
Introduction and Prerequisites
Unlike triggers where you can list all the triggers using the SHOW TRIGGERS statement, you can’t list all the stored procedures using a specific statement. However, MySQL does provide the SHOW PROCEDURE STATUS statement to list all the stored procedures from the databases.
If you are new to the stored procedure topic then you can read our detailed guide on introduction to the stored procedure. It will help you to get all the necessary information that you should know about stored procedures.
Syntax to List Procedures
MySQL provides us with the SHOW PROCEDURE STATUS statement to display information about stored procedures. However, if you enter the command without the WHERE or LIKE clause, you may get hundreds of lines of output with unnecessary information.
If you want to list the details of only stored procedures then the following is the syntax that you should use-
SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE search_condition];Code language: SQL (Structured Query Language) (sql)Where,
- SHOW PROCEDURE STATUS- It is a statement to display the details of procedures.
- LIKE – The LIKE operator is used to match the given pattern with procedure names.
- WHERE – The WHERE clause can be used to display results using more general conditions. Here is the list of extensions to the SHOW statement.
Examples using SELECT PROCEDURE STATUS statement
We will now see examples of listing the stored procedures using the above syntax.
Example 1 – List All Stored Procedures
To display all stored procedures, you can use the following syntax.
SHOW PROCEDURE STATUS;Code language: SQL (Structured Query Language) (sql)When we enter the following command, see what we get.
SHOW PROCEDURE STATUS \G;Code language: SQL (Structured Query Language) (sql)The \G is used to get the result in a new line. The snapshot below is a partial result we captured. When you enter the command on the CLI, you get the result of thousands of lines.
Example 2 – List Stored Procedures using WHERE clause
The WHERE clause can be used to list stored procedures from a specific database. Following is the syntax for it-
SHOW PROCEDURE STATUS WHERE search_condition;Code language: SQL (Structured Query Language) (sql)For example-
SHOW PROCEDURE STATUS WHERE db = 'journaldev' \G;Code language: SQL (Structured Query Language) (sql)We are trying to display all the stored procedures from the database ‘journaldev’.
Example 3 – List Stored Procedures using LIKE Operator
The LIKE operator is used to match the given pattern with procedure names. Following is the syntax to use the LIKE operator with the SHOW PROCEDURE STATUS statement.
SHOW PROCEDURE STATUS LIKE '%pattern%'Code language: SQL (Structured Query Language) (sql)For example-
SHOW PROCEDURE STATUS like '%lap%' \G;Code language: SQL (Structured Query Language) (sql)The above query will list all stored procedures which contain the word “lap” in their names.
List Stored Procedures using Data Dictionary
You can find the names of stored procedures in your MySQL server using the “routines” table in the “information_schema” database.
Use the following syntax to list all stored procedures-
SELECT routine_name
FROM information_schema.routines
WHERE routine_type = 'PROCEDURE' AND routine_schema = '<database_name>';Code language: SQL (Structured Query Language) (sql)In the above syntax, you just need to replace “database_name” with your actual database name.
For example-
SELECT routine_name
FROM information_schema.routines
WHERE routine_type = 'PROCEDURE' AND routine_schema = 'journaldev'Code language: SQL (Structured Query Language) (sql)In the above query, we are displaying only names of stored procedures from the database “journaldev”.
Display Stored Procedures using MySQL Workbench
If you don’t use MySQL CLI and want to view all stored procedures, you can use MySQL workbench. Follow the given steps-
- Open the workbench and make a connection with the MySQL server.
- On the left side, you will see all the database names.
- Click on the database name and there you will see the option “stored procedures”.
- When you click on it, you will get all stored procedures names.
Conclusion
So these are some ways to list stored procedures from your MySQL server. We have explained every possible way to display stored procedures and you can use any of them at your convenience. I hope you have understood and liked this tutorial. Stay connected with mysqlcode.com for more interesting tutorials!
References
MySQL official documentation on show procedure status.