存储过程/函数列表 Mysql 命令行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/733349/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
List of Stored Procedures/Functions Mysql Command Line
提问by systemsfault
How can I see the list of the stored procedures or stored functions in mysql command line like show tables;or show databases;commands.
如何在 mysql 命令行中查看存储过程或存储函数的列表，如show tables;或show databases;命令。
回答by fredrik
SHOW PROCEDURE STATUS;
SHOW FUNCTION STATUS;
回答by systemsfault
show procedure status
will show you the stored procedures.
将向您展示存储过程。
show create procedure MY_PROC
will show you the definition of a procedure. And
将向您展示过程的定义。和
help show
will show you all the available options for the showcommand.
将向您显示该show命令的所有可用选项。
回答by Praveenkumar_V
For view procedure in name wise
对于名称明智的查看过程
select name from mysql.proc
below code used to list all the procedure and below code is give same result as show procedure status
下面的代码用于列出所有程序，下面的代码给出与显示程序状态相同的结果
select * from mysql.proc
回答by sassman
A more specific way:
更具体的方法：
SHOW PROCEDURE STATUS
WHERE Db = DATABASE() AND Type = 'PROCEDURE'
回答by Drarok
As mentioned above,
正如刚才提到的，
show procedure status;
Will indeed show a list of procedures, but shows allof them, server-wide.
确实会显示程序列表，但会显示服务器范围内的所有程序。
If you want to see just the ones in a single database, try this:
如果您只想查看单个数据库中的那些，请尝试以下操作：
SHOW PROCEDURE STATUS WHERE Db = 'databasename';
回答by macio.Jun
Alternative:
选择：
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
回答by John Haugeland
My preference is for something that:
我的偏好是：
- Lists both functions and procedures,
- Lets me know which are which,
- Gives the procedures' names and types and nothing else,
- Filters results by the current database, not the current definer
- Sorts the result
- 列出函数和过程，
- 让我知道哪些是哪些，
- 给出程序的名称和类型，仅此而已，
- 按当前数据库过滤结果，而不是当前定义者
- 对结果进行排序
Stitching together from other answers in this thread, I end up with
从这个线程中的其他答案拼接在一起，我最终得到
select
name, type
from
mysql.proc
where
db = database()
order by
type, name;
... which ends you up with results that look like this:
...最终得到的结果如下所示：
mysql> select name, type from mysql.proc where db = database() order by type, name;
+------------------------------+-----------+
| name | type |
+------------------------------+-----------+
| get_oldest_to_scan | FUNCTION |
| get_language_prevalence | PROCEDURE |
| get_top_repos_by_user | PROCEDURE |
| get_user_language_prevalence | PROCEDURE |
+------------------------------+-----------+
4 rows in set (0.30 sec)
回答by John Haugeland
use this:
用这个：
SHOW PROCEDURE STATUS;
回答by Optimizer
Shows all the stored procedures:
显示所有存储过程：
SHOW PROCEDURE STATUS;
Shows all the functions:
显示所有功能：
SHOW FUNCTION STATUS;
Shows the definition of the specified procedure:
显示指定过程的定义：
SHOW CREATE PROCEDURE [PROC_NAME];
Shows you all the procedures of the given database:
显示给定数据库的所有过程：
SHOW PROCEDURE STATUS WHERE Db = '[db_name]';
回答by trapper_hag
A variation on Praveenkumar_V's post:
Praveenkumar_V 帖子的变体：
SELECT `name` FROM mysql.proc WHERE db = 'dbname' AND `type` = 'PROCEDURE';
SELECT `name` FROM mysql.proc WHERE db = 'dbname' AND `type` = 'FUNCTION';
..and this because I needed to save time after some housekeeping:
..这是因为我需要在一些家务之后节省时间：
SELECT CONCAT(
"GRANT EXECUTE ON PROCEDURE `"
,`name`
,"` TO username@'%'; -- "
,`comment`
)
FROM mysql.proc
WHERE db = 'dbname'
AND `type` = 'PROCEDURE';
SELECT CONCAT(
"GRANT EXECUTE ON FUNCTION `"
,`name`
,"` TO username@'%'; -- "
,`comment`
)
FROM mysql.proc
WHERE db = 'dbname'
AND `type` = 'FUNCTION';
