blocks|key|1157858|text|SHOW+CREATE+PROCEDURE+<name>|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1157859|返回使用CREATE+PROCEDURE语句创建的以前定义的存储过程的文本。将存储函数的PROCEDURE替换为FUNCTION。|unstyled|offset|length|style|CODE|1157860|entityMap^0|0|4|G|18|9|1K|8|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|N|8|@$G|O|H|P|I|J]|$G|Q|H|R|I|J]|$G|S|H|T|I|J]]|9|@]|A|$]]|$1|K|3|-4|5|F|7|U|8|@]|9|@]|A|$]]]|L|$]]

<pre><code>SHOW CREATE PROCEDURE &lt;name&gt;
</code></pre>

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

blocks|key|1157896|text|SHOW+CREATE+PROCEDURE+proc_name;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1157897|返回proc_name的定义|unstyled|offset|length|style|CODE|1157898|entityMap^0|0|2|9|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|N|8|@$G|O|H|P|I|J]]|9|@]|A|$]]|$1|K|3|-4|5|F|7|Q|8|@]|9|@]|A|$]]]|L|$]]

<pre><code>SHOW CREATE PROCEDURE proc_name;
</code></pre>

returns the definition of <code>proc_name</code>

blocks|key|1373104|text|类似于：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1373105|DELIMITER+//

CREATE+PROCEDURE+alluser()
BEGIN
+++SELECT+*
+++FROM+users;
END+//

DELIMITER+;|code-block|syntax|javascript|1373106|比：|1373107|SHOW+CREATE+PROCEDURE+alluser|1373108|给出结果：|1373109|'alluser',+'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER',+'CREATE+DEFINER=`root`@`localhost`+PROCEDURE+`alluser`()
BEGIN
+++SELECT+*
+++FROM+users;
END'|1373110|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

something like:

<pre><code>DELIMITER //

CREATE PROCEDURE alluser()
BEGIN
 SELECT *
 FROM users;
END //

DELIMITER ;
</code></pre>

than:

<pre><code>SHOW CREATE PROCEDURE alluser
</code></pre>

gives result:

<pre><code>'alluser', 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER', 'CREATE DEFINER=`root`@`localhost` PROCEDURE `alluser`()
BEGIN
 SELECT *
 FROM users;
END'
</code></pre>

blocks|key|1157968|text|您可以使用以下命令：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1157969|SELECT+ROUTINE_DEFINITION+FROM+INFORMATION_SCHEMA.ROUTINES
WHERE+ROUTINE_SCHEMA+=+'yourdb'+AND+ROUTINE_TYPE+=+'PROCEDURE'+AND+ROUTINE_NAME+=+"procedurename";|code-block|syntax|javascript|1157970|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can use this:

<pre><code>SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'yourdb' AND ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = "procedurename";
</code></pre>

blocks|key|1373174|text|如果您想了解过程列表，可以运行以下命令：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1373175|show+procedure+status;|code-block|syntax|javascript|1373176|它将为您提供过程及其定义器的列表，然后您就可以运行show+create+procedure+<procedurename>;|offset|length|style|CODE|1373177|entityMap^0|0|0|P|12|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

If you want to know the list of procedures you can run the following command -

<pre><code>show procedure status;
</code></pre>

It will give you the list of procedures and their definers
Then you can run the <code>show create procedure &lt;procedurename&gt;;</code>

blocks|key|1158060|text|太好了，试试吧：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1158061|SELECT+ROUTINE_DEFINITION+FROM+INFORMATION_SCHEMA.ROUTINES
++++WHERE+ROUTINE_SCHEMA+=+'yourdb'+AND+ROUTINE_TYPE+=+'PROCEDURE'+AND+ROUTINE_NAME+=+"procedurename";|code-block|syntax|javascript|1158062|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Perfect, try it:

<pre><code>SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES
 WHERE ROUTINE_SCHEMA = 'yourdb' AND ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = "procedurename";
</code></pre>

blocks|key|1373308|text|如果您想要获得所有产品的概述，或者遇到仅获取SHOW+CREATE+procedure所示的过程头的问题，则可以使用另一个快速而老套的解决方案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1373309|mysqldump+--user=<user>+-p+--no-data+--routines+<database>|code-block|syntax|javascript|1373310|它还将导出表描述，但不会导出数据。对于探索未知或遗忘的模式很有效……;)|1373311|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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:

<pre><code>mysqldump --user=&lt;user&gt; -p --no-data --routines &lt;database&gt;
</code></pre>

It will export the table descriptions as well, but no data. Works well for sniffing around unknown or forgotten schemas... ;)

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

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

View stored procedure/function definition in MySQL

SQL Server

MySQL

查看存储过程或函数的定义的MySQL命令是什么，类似于Microsoft SQL Server中的sp_helptext？我知道SHOW PROCEDURE STATUS将显示可用过程的列表。我需要查看单个过程的定义。

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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