Skip to main content

SHOW FUNCTION STATUS

The SHOW FUNCTION STATUS statement displays information about stored functions, including the database, name, type, creator, creation and modification dates, and character set.

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

SHOW FUNCTION STATUS
[LIKE 'pattern' | WHERE expr]

To use 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.

The LIKE clause (if present) specifies the name of the function to match. You can also use the WHERE clause to select rows.

The SHOW FUNCTION STATUS statement outputs the following information:

  • Db: the name of the database where the stored program is stored.

  • Name: the name of the stored program.

  • Type: the type of the stored program.

  • Definer: the creator.

  • Modified: the modification time.

  • Created: the creation time.

  • Security_type: the SQL security type.

  • Comment: the comment.

  • character_set_client: the value of the character_set_client system variable in the current session when the stored program was created.

  • collation_connection: the value of the collation_connection system variable in the current session when the stored program was created.

  • Database Collation: the collation of the database associated with the stored program.

Information about stored programs can also be obtained from the INFORMATION_SCHEMA PARAMETERS and INFORMATION_SCHEMA ROUTINES tables. For more information, see INFORMATION_SCHEMA PARAMETERS and INFORMATION_SCHEMA ROUTINES.

Here is an example of the SHOW FUNCTION STATUS statement:

SHOW FUNCTION STATUS LIKE 'func1'\G
*************************** 1. row ***************************
                  Db: test
                Name: func1
                Type: FUNCTION
             Definer: 'root'@'%'
            Modified: 2022-05-26 18:07:51.994639
             Created: 2022-05-26 18:07:51.994639
       Security_type: DEFINER
             Comment: NULL
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_general_ci