MySQL List All Procedures

Sreeram Sreenivasan

Sometimes you may need to show all stored procedures in MySQL. Here’s how to list all procedures in MySQL using SHOW PROCEDURE command.

 

MySQL List All Procedures

You can easily list all procedures in MySQL using SHOW PROCEDURE command

Here’s the syntax of SHOW PROCEDURE statement

SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE search_condition]

The above statement returns many attributes of stored procedures such as its name, database, creation date, modified date, etc.

However, it will list all procedures that you have access to.

Bonus Read : MySQL Reset Root Password

Here’s an example of SHOW PROCEDURE STATUS statement

mysql> show procedure status;
+--------+---------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db     | Name          | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+--------+---------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| sample | counter       | PROCEDURE | root@localhost | 2020-07-20 09:14:11 | 2020-07-20 09:14:11 | DEFINER       |         | cp850                | cp850_general_ci     | latin1_swedish_ci  |
| sample | fill_calendar | PROCEDURE | root@localhost | 2020-04-17 16:44:12 | 2020-04-17 16:44:12 | DEFINER       |         | cp850                | cp850_general_ci     | latin1_swedish_ci  |
| sample | get_count     | PROCEDURE | root@localhost | 2020-07-20 09:07:32 | 2020-07-20 09:07:32 | DEFINER       |         | cp850                | cp850_general_ci     | latin1_swedish_ci  |
| sample | get_product   | PROCEDURE | root@localhost | 2020-07-20 09:00:00 | 2020-07-20 09:00:00 | DEFINER       |         | cp850                | cp850_general_ci     | latin1_swedish_ci  |
| sample | Get_Products  | PROCEDURE | root@localhost | 2020-07-27 09:13:15 | 2020-07-27 09:13:15 | DEFINER       |         | cp850                | cp850_general_ci     | latin1_swedish_ci  |
+--------+---------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+

If you want to list all procedures pertaining to a specific database, you can do so using WHERE clause in the SHOW PROCEDURE statement.

SHOW PROCEDURE STATUS where search_condition

Here’s the SQL query to list all procedures in sample_db database.

mysql> show procedure status where db='sample_db';

Bonus Read : How to Delete Stored Procedure in MySQL

 

Similarly, if you want to show all stored procedures whose name contains a specific pattern, you can use the LIKE clause, as shown,

SHOW PROCEDURE STATUS LIKE '%pattern%';

Here’s the SQL query to list all stored procedures that contain the word ‘get’

mysql> show procedure status like '%get%';

 

Hopefully, now you can easily list all stored procedures in MySQL.

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

Related posts:

How to Enable SSL/TLS for MySQL in Ubuntu
How To Rename Table in MySQL
Calculate decile from frequency in MySQL