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:SHOWPROCEDURESTATUS[LIKE'pattern'|WHEREexpr];
TheSHOW PROCEDURE STATUSstatement 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 orWHERE clauseto 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 theSHOW PROCEDURE STATUSstatement as follows:SHOWPROCEDURESTATUS;
If you want to show just stored procedure in a particular database, you can use theWHEREclause in theSHOW PROCEDURE STATUSstatement:SHOWPROCEDURESTATUSWHEREdb='classicmodels';
If you want to show stored procedures that have a particular pattern e.g., its name containsproduct, you can use the LIKE operator as the following command:SHOWPROCEDURESTATUSWHEREnameLIKE'%product%'
Displaying stored procedure’s source code
To display source code of a particular stored procedure, you use theSHOW CREATE PROCEDUREstatement as follows:SHOWCREATEPROCEDUREstored_procedure_name
You specify the name of the stored procedure after theSHOW CREATE PROCEDUREkeywords. For example, to display the code of theGetAllProductsstored procedure, you use the following statement:SHOWCREATEPROCEDUREGetAllProducts
In this tutorial, you have learned some useful statements includingSHOW PROCEDURE STATUSandSHOW CREATE PROCEDUREstatements to list stored procedures in a database and get the source code of the stored procedure.
Related TutorialsGetting Started with MySQL Stored Procedures
MySQL Stored Procedure Variables
原文链接：http://outofmemory.cn/mysql/procedure/listing-stored-procedures-in-mysql-database
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
