The SHOW CREATE FUNCTION statement is used to display information about created stored functions.
Syntax
SHOW CREATE FUNCTION func_name
Parameters
Parameter |
Note |
|---|---|
| func_name | The name of the stored function to be viewed. You can use database_name.func_name Specify the database. |
To use this statement, you must be the user defined by DEFINER and have global-level SELECT privilege, or have CREATE ROUTINE, ALTER ROUTINE, or EXECUTE privilege on the routine. If you only have CREATE ROUTINE, ALTER ROUTINE, or EXECUTE privilege, the value in the Create Function field is displayed as NULL.
The SHOW CREATE FUNCTION statement outputs the following information:
Function: the name of the stored function.sql_mode: The SQL mode that takes effect when executing stored functions.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 is 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 associated with the stored function.
Information about stored functions can also be obtained from the INFORMATION_SCHEMA ROUTINES table. For more information, see INFORMATION_SCHEMA PARAMETERS.
Examples
View the definition of a stored function.
obclient> DELIMITER //
obclient> CREATE FUNCTION my_func(c1 CHAR(20)) RETURNS CHAR(50) DETERMINISTIC RETURN CONCAT('Thank ', c1, '!') //
obclient> DELIMITER ;
obclient> SHOW CREATE FUNCTION my_func;
The query result is as follows:
+----------+----------+-----------------+-----------------------+----------------------+--------------------+
| 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