mysql列出数据库中所有的存储过程

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

• Getting Started with MySQL Stored Procedures

• MySQL Stored Procedure Variables

原文链接：http://outofmemory.cn/mysql/procedure/listing-stored-procedures-in-mysql-database