Press CTRL+C to copySHOW CREATE PROCEDURE proc_name
This statement is a MySQL extension. It returns the exact string
that can be used to re-create the named stored procedure. A
similar statement, SHOW CREATE
FUNCTION
, displays information about stored functions
(see Section 15.7.7.9, “SHOW CREATE FUNCTION Statement”).
To use either statement, you must be the user named as the
routine DEFINER
, have the
SHOW_ROUTINE
privilege, have the
SELECT
privilege at the global
level, or have the CREATE
ROUTINE
, ALTER ROUTINE
,
or EXECUTE
privilege granted at a
scope that includes the routine. The value displayed for the
Create Procedure
or Create
Function
field is NULL
if you have
only CREATE ROUTINE
,
ALTER ROUTINE
, or
EXECUTE
.
Press CTRL+C to copymysql> 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
is the session value of
the character_set_client
system
variable when the routine was created.
collation_connection
is the session value of
the collation_connection
system
variable when the routine was created. Database
Collation
is the collation of the database with which
the routine is associated.