The SHOW FUNCTION STATUS statement displays information about stored functions, including the database, name, type, creator, creation and modification dates, and character set.
Syntax
SHOW FUNCTION STATUS
[LIKE 'pattern' | WHERE expr]
Parameters
| Parameter | Description |
|---|---|
| LIKE 'pattern' | Optional. Specifies the function name pattern to match. You can use the % and _ wildcards. |
| WHERE expr | Optional. Specifies conditions to select rows using the WHERE clause. |
To use this statement, you must be the user defined by the DEFINER option and have the SHOW_ROUTINE privilege, have the global SELECT privilege, or have the CREATE ROUTINE, ALTER ROUTINE, or EXECUTE privilege on the routine.
The SHOW FUNCTION STATUS statement outputs the following information:
Db: The name of the database where the stored function is located.Name: The name of the stored function.Type: The type of the stored program, which isFUNCTION.Definer: The creator of the stored function.Modified: The modification time.Created: The creation time.Security_type: The SQL security type.Comment: The comment.character_set_client: The value of thecharacter_set_clientsystem variable in the current session when the stored function was 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 PARAMETERS and INFORMATION_SCHEMA ROUTINES tables. For more information, see INFORMATION_SCHEMA PARAMETERS and INFORMATION_SCHEMA ROUTINES.
Examples
View the status of all stored functions.
obclient> SHOW FUNCTION STATUS;
View the status of stored functions that match a specific pattern:
obclient> SHOW FUNCTION STATUS LIKE 'func%';
The query result is as follows:
+------+-------+----------+---------------+---------------------+---------------------+---------------+-------+--------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+-------+----------+---------------+---------------------+---------------------+---------------+-------+--------------------+----------------------+--------------------+
| test | func1 | FUNCTION | 'root'@'%' | 2022-05-26 18:07:51 | 2022-05-26 18:07:51 | DEFINER | NULL | utf8mb4 | utf8mb4_general_ci | utf8mb4_general_ci |
+------+-------+----------+---------------+---------------------+---------------------+---------------+-------+--------------------+----------------------+--------------------+
1 row in set