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!
