查看 MySQL 中的存储过程/函数定义

什么是 MySQL 命令来查看存储过程或函数的定义，类似于 Microsoft SQL Server 中的 sp_helptext？

我知道 SHOW PROCEDURE STATUS会显示可用程序的列表。我需要看到单个程序的定义。

196705 次浏览

小开

最佳答案

SHOW CREATE PROCEDURE <name>

Returns the text of a previously defined stored procedure that was created using the CREATE PROCEDURE statement. Swap PROCEDURE for FUNCTION for a stored function.

小开

SHOW CREATE PROCEDURE proc_name;

returns the definition of proc_name

小开

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'

小开

You can use this:

SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'yourdb' AND ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = "procedurename";

小开

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>;

小开

Perfect, try it:

SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'yourdb' AND ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = "procedurename";

小开

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:

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... ;)

小开

You can use table proc in database mysql:

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> GRANT SELECT ON mysql.proc TO 'youruser'@'yourhost' IDENTIFIED BY 'yourpass' ;