SHOW CREATE FUNCTION

The SHOW CREATE FUNCTION statement displays information about a stored function.

Syntax

SHOW CREATE FUNCTION func_name

Parameters

To execute this statement, you must be the user defined by the DEFINER clause and have the SHOW_ROUTINE privilege, or you must have the global SELECT privilege, or you must have the CREATE ROUTINE, ALTER ROUTINE, or EXECUTE privilege on the routine. If you only have the CREATE ROUTINE, ALTER ROUTINE, or EXECUTE privilege, the value of the Create Function field is NULL.

The SHOW CREATE FUNCTION statement outputs the following information:

• Function: The name of the stored function.
• sql_mode: The SQL mode in effect when the stored function is executed.
• Create Function: The CREATE FUNCTION statement used to define the stored function.
• character_set_client: The value of the character_set_client system variable in the current session when the stored function was created.
• collation_connection: The value of the collation_connection system variable in the current session when the stored function was created.
• Database Collation: The collation of the database associated with the stored function.

You can also obtain information about stored functions 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