SHOW CREATE PROCEDURE proc_name

该语句是 MySQL 扩展。它返回可以重新创建命名存储过程的确切字符串。类似的语句，显示创建函数，显示存储函数的信息（见第 15.7.7.9 节，“显示创建函数语句”）。

要使用这两个语句，必须是命名的存储过程的定义者，拥有SHOW_ROUTINE权限，拥有SELECT权限在全局级别，或者拥有CREATE ROUTINE、ALTER ROUTINE或EXECUTE权限在包括该过程的范围内。该字段的显示值为 NULL，如果您只有CREATE ROUTINE、ALTER ROUTINE或EXECUTE权限。

mysql> SHOW CREATE PROCEDURE test.citycount\G
*************************** 1. row ***************************
           Procedure: citycount
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,
                      NO_ZERO_IN_DATE,NO_ZERO_DATE,
                      ERROR_FOR_DIVISION_BY_ZERO,
                      NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`me`@`localhost`
                      PROCEDURE `citycount`(IN country CHAR(3), OUT cities INT)
                      BEGIN
                        SELECT COUNT(*) INTO cities FROM world.city
                        WHERE CountryCode = country;
                      END
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci

mysql> SHOW CREATE FUNCTION test.hello\G
*************************** 1. row ***************************
            Function: hello
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,
                      NO_ZERO_IN_DATE,NO_ZERO_DATE,
                      ERROR_FOR_DIVISION_BY_ZERO,
                      NO_ENGINE_SUBSTITUTION
     Create Function: CREATE DEFINER=`me`@`localhost`
                      FUNCTION `hello`(s CHAR(20))
                      RETURNS char(50) CHARSET utf8mb4
                      DETERMINISTIC
                      RETURN CONCAT('Hello, ',s,'!')
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci

character_set_client 是创建过程时会话的 character_set_client 系统变量的值。collation_connection 是创建过程时会话的 collation_connection 系统变量的值。Database Collation 是与该过程关联的数据库的排序规则。