查看 MySQL 中的存储过程/函数定义
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1968773/
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
View stored procedure/function definition in MySQL
提问by Srinivas M.V.
What is the MySQL command to view the definition of a stored procedure or function, similar to sp_helptextin Microsoft SQL Server?
什么是 MySQL 命令来查看存储过程或函数的定义，类似于sp_helptextMicrosoft SQL Server？
I know that SHOW PROCEDURE STATUSwill display the list of the procedures available. I need to see a single procedure's definition.
我知道那SHOW PROCEDURE STATUS会显示可用程序的列表。我需要查看单个过程的定义。
回答by afftee
SHOW CREATE PROCEDURE <name>
Returns the text of a previously defined stored procedure that was created using the CREATE PROCEDUREstatement. Swap PROCEDUREfor FUNCTIONfor a stored function.
返回使用该CREATE PROCEDURE语句创建的先前定义的存储过程的文本。交换PROCEDURE了FUNCTION一个存储功能。
回答by Haim Evgi
You can use this:
你可以使用这个：
SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'yourdb' AND ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = "procedurename";
回答by Sibashish Pujari
If you want to know the list of procedures you can run the following command -
如果您想知道程序列表，可以运行以下命令 -
show procedure status;
It will give you the list of procedures and their definers
Then you can run the show create procedure <procedurename>;
它将为您提供程序列表及其定义者然后您可以运行 show create procedure <procedurename>;
回答by valli
SHOW CREATE PROCEDURE proc_name;
returns the definition of proc_name
返回定义 proc_name
回答by Michel
something like:
就像是：
DELIMITER //
CREATE PROCEDURE alluser()
BEGIN
SELECT *
FROM users;
END //
DELIMITER ;
than:
比：
SHOW CREATE PROCEDURE alluser
gives result:
给出结果：
'alluser', 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER', 'CREATE DEFINER=`root`@`localhost` PROCEDURE `alluser`()
BEGIN
SELECT *
FROM users;
END'
回答by ISparkes
An alternative quick and hacky solution if you want to get an overview of all the produres there are, or run into the issue of only getting the procedure header shown by SHOW CREATE PROCEDURE:
如果您想了解所有产品的概述，或者遇到仅获取 SHOW CREATE PROCEDURE 显示的过程标头的问题，则另一种快速而笨拙的解决方案：
mysqldump --user=<user> -p --no-data --routines <database>
It will export the table descriptions as well, but no data. Works well for sniffing around unknown or forgotten schemas... ;)
它也会导出表描述，但不会导出数据。适用于嗅探未知或遗忘的模式......;）
回答by Omidreza Bagheri
You can use table procin database mysql:
您可以在数据库mysql 中使用 table proc：
mysql> SELECT body FROM mysql.proc
WHERE db = 'yourdb' AND name = 'procedurename' ;
Note that you must have a grant for select to mysql.proc:
请注意，您必须获得对mysql.proc 的select授权：
mysql> GRANT SELECT ON mysql.proc TO 'youruser'@'yourhost' IDENTIFIED BY 'yourpass' ;
回答by Sumit
Perfect, try it:
完美，试试看：
SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'yourdb' AND ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = "procedurename";
