SHOW CREATE FUNCTION

You can use the SHOW CREATE FUNCTION statement to view the information about a stored function.

The syntax is as follows:

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 CREATE 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: the CREATE FUNCTION statement that defines 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 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.

Here is an 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