Skip to main content

SHOW CREATE FUNCTION

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

The syntax of the SHOW CREATE FUNCTION statement is as follows:

SHOW CREATE FUNCTION func_name

To execute this statement, you must be the user defined by the DEFINER clause, have the SHOW_ROUTINE privilege, have the global SELECT privilege, or have the CREATE ROUTINE, ALTER ROUTINE, or EXECUTE privilege on the routine. If you have only 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 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 is created.

  • collation_connection: the value of the collation_connection system variable in the current session when the stored function is 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.

Here is an example of the SHOW CREATE FUNCTION statement:

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