View MySQL Stored Procedures using SHOW PROCEDURE STATUS Statement

List Stored Procedures In Mysql

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];

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;

When we enter the following command, see what we get.

SHOW PROCEDURE STATUS \G;

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.

Show Procedure Status Output
Show Procedure Status Output

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;

For example-

SHOW PROCEDURE STATUS WHERE db = 'journaldev' \G;

We are trying to display all the stored procedures from the database ‘journaldev’.

Show Procedure Status Using Where Clause
Show Procedure Status Using Where Clause

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%'

For example-

SHOW PROCEDURE STATUS like '%lap%' \G;

The above query will list all stored procedures which contain the word “lap” in their names.

Show Procedure Status Using Like Clause
Show Procedure Status Using Like Clause

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>';

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'

In the above query, we are displaying only names of stored procedures from the database “journaldev”.

Show Procedures Using Data Dictionary
Show Procedures Using Data Dictionary

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.