Summary: in this tutorial, we will show you how to list all stored procedures in a MySQL database, and introduce you to a very useful statement that displays stored procedure’s source code.
MySQL provides us with several useful statements that help us to 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];
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;
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';
If you want to show stored procedures that have a particular pattern e.g., its name contains product, you can use the LIKE operator as the following command:
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
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
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.
Related Tutorials
原文链接：http://outofmemory.cn/mysql/procedure/listing-stored-procedures-in-mysql-database