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

最新推荐文章于 2024-08-04 16:50:27 发布

IT小霸王

最新推荐文章于 2024-08-04 16:50:27 发布

阅读量136

点赞数

文章标签：列出mysql的所有存储过程

本文链接：https://blog.csdn.net/weixin_29031057/article/details/113290490

版权

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 WHEREclause 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.