Listing Stored Procedures in a MySQL Database

MySQL provides you with several useful statements that help you manage stored procedures effectively. Those statements include listing stored procedures and showing the stored procedure’s source code.

Displaying stored procedures characteristics

To display characteristics of a stored procedure, you use the following statement:

SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE expr];

1	SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE expr];

The SHOW PROCEDURE STATUS statement is a MySQL extension to SQL standard. This statement gives you the stored procedure’s characteristics including database, stored procedure name, type, creator and so on.
You can use LIKE or WHERE clause to filter out the stored procedure based on various criteria.
To list all stored procedures of the databases that you have the privilege to access, you use the  SHOW PROCEDURE STATUS statement as follows:

SHOW PROCEDURE STATUS;

1	SHOW PROCEDURE STATUS;

If you want to show just stored procedure in a particular database, you can use the WHERE clause in the  SHOW PROCEDURE STATUS statement:

SHOW PROCEDURE STATUS WHERE db = 'classicmodels';

1	SHOW PROCEDURE STATUS WHERE db = 'classicmodels';

If you want to show stored procedures that have a particular pattern e.g., its name has word product, you can use the LIKE operator as the following command:

SHOW PROCEDURE STATUS WHERE name LIKE '%product%'

1	SHOW PROCEDURE STATUS WHERE name LIKE '%product%'

Displaying stored procedure’s source code

To display source code of a particular stored procedure, you use the SHOW CREATE PROCEDURE statement as follows:

SHOW CREATE PROCEDURE stored_procedure_name

1	SHOW CREATE PROCEDURE stored_procedure_name

You specify the name of the stored procedure after the  SHOW CREATE PROCEDURE keywords. For example, to display the code of the GetAllProducts stored procedure, you use the following statement:

SHOW CREATE PROCEDURE GetAllProducts

1	SHOW CREATE PROCEDURE GetAllProducts

In this tutorial, you have learned some useful statements including  SHOW PROCEDURE STATUS and  SHOW CREATE PROCEDURE statements to list stored procedures in a database and get the source code of the stored procedure