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

What is the MySQL command to view the definition of a stored procedure or function, similar to sp_helptext in Microsoft SQL Server?

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

I know that SHOW PROCEDURE STATUS will display the list of the procedures available. I need to see a single procedure's definition.

我知道显示程序状态将显示可用程序的列表。我需要看到一个过程的定义。

8 个解决方案

#1

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.

返回使用CREATE procedure语句创建的先前定义的存储过程的文本。为存储函数的函数交换过程。

#2

You can use this:

您可以使用:

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

#3

SHOW CREATE PROCEDURE proc_name;

returns the definition of proc_name

返回proc_name的定义

#4

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过程< programurename >;

#5

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'

#6

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

它也将导出表描述，但不导出数据。可以很好地嗅探未知或遗忘的模式……,)

#7

You can use table proc in database mysql:

可以在数据库mysql中使用表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:

请注意，您必须为select to sqmyl .proc提供一个授权:

mysql> GRANT SELECT ON mysql.proc TO 'youruser'@'yourhost' IDENTIFIED BY 'yourpass' ;

#8

-2

Perfect, try it:

完美,试一试:

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

#1

SHOW CREATE PROCEDURE <name>