The SHOW CREATE FUNCTION statement shows the creation information about a stored function.
Syntax:
SHOW CREATE FUNCTION func_name
To use this statement, you must be the user defined by DEFINER and have the SHOW_ROUTINE privilege and the global SELECT privilege, or the CREATE ROUTINE, ALTER ROUTINE, or EXECUTE privilege on this routine. If you have only the REATE ROUTINE, ALTER ROUTINE, or EXECUTE privilege, the value of the Create Procedure or Create Function field is NULL.
The output of the SHOW CREATE FUNCTION statement contains the following fields:
Function: the name of the stored function.sql_mode: the SQL mode used when the stored function is executed.Create Function: theCREATE FUNCTIONstatement that defines the stored function.character_set_client: the value of thecharacter_set_clientsystem variable in the current session when the stored function was created.collation_connection: the value of thecollation_connectionsystem variable in the current session when the stored function was created.Database Collation: the collation of the database to which the stored function is associated.
You can also obtain the information about a stored function from the INFORMATION_SCHEMA ROUTINES table. For more information, see INFORMATION_SCHEMA PARAMETERS.
Example:
obclient> SHOW CREATE FUNCTION my_func;
+----------+----------+-----------------+-----------------------+----------------------+--------------------+
| Function | sql_mode | Create Function | character_set_client | collation_connection | Database Collation |
+----------+----------+-----------------+-----------------------+----------------------+--------------------+
| my_func | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE | CREATE FUNCTION `test`.`my_func`( `c1` char(20)) RETURNS char(50) RETURN CONCAT('Thank ',c1,'!') | utf8mb4 | utf8mb4_general_ci | utf8mb4_general_ci |
+----------+----------+-----------------+-----------------------+----------------------+--------------------+
1 row in set