How to Get List of Stored Procedures & Functions in MySQL

Stored Procedures and functions make it easy to execute frequent queries and tasks in MySQL. Over time, as users build more and more procedures and functions in their database systems it can become difficult to keep track of them. In such cases, you may need to get list of stored procedures & functions in MySQL. This is also required for new users who join a team of developers who have already done quite a bit of work before them. In this article, we will learn how to get list of stored procedures and functions in MySQL.

How to Get List of Stored Procedures & Functions in MySQL

It is very easy to view all the stored procedures and functions that you have access to in your MySQL system. Just log into MySQL console and run the following queries to see stored procedures and functions defined in your database server.

Here is the query to view stored procedures.

SHOW PROCEDURE STATUS;

If you want to view stored procedure information pertaining to specific database only, use the following query.

SHOW PROCEDURE STATUS WHERE Db = '[db_name]';

Here is the query to view functions.

If you also want to see the procedure definitions, run the following query. Replace PROC_NAME with the name of stored procedure.

show create procedure PROC_NAME

If you are interested in learning about available options for show command, run the following query.

help show

Alternatively, you can also use the following query to view the names of all stored procedures in your system.

select name from mysql.proc

If you want more details about stored procedures, run the following query instead.

select * from mysql.proc 

Both the above queries refer to system database mysql created at the time of MySQL installation. It is automatically created and updated by MySQL server.

If none of the above queries work for you, try the following query to get list of stored procedure. It uses another system database INFORMATION_SCHEMA automatically created and maintained by MySQL server.

SELECT * FROM INFORMATION_SCHEMA.ROUTINES

In this article, we have learnt how to get list of stored procedures and functions in MySQL.

Also read:

How to Select Rows Where Date Matches Today in MySQL
How to Copy Row and Insert to Same Table in MySQL
How to Run SQL Script in MySQL
How to Find Number of Columns in Table
How to Search for Text in Every Field of Database