SHOW FUNCTION STATUS

Description

SHOW FUNCTION STATUS displays information about all functions in the database, including function name, database name, creation time, etc.

The SHOW FUNCTION STATUS command only shows user-defined functions, not system functions.

Syntax

  1. > SHOW FUNCTION STATUS
  2.     [LIKE 'pattern' | WHERE expr]

Explanations

LIKE 'pattern' is an optional argument to filter the functions to display. pattern is a string that can use % and _ wildcards. For example, to display all functions starting with my_function, the following command can be used:

  1. SHOW FUNCTION STATUS LIKE 'my_function%';

The output will include the function name, database name, type, creation time, and modification time.

Examples

  1. mysql> create function twosum (x float, y float) returns float language sql as 'select $1 + $2' ;
  2. Query OK, 0 rows affected (0.03 sec)
  4. mysql> create function mysumtable(x int) returns int language sql as 'select mysum(test_val, id) from tbl1 where id = $1';
  5. Query OK, 0 rows affected (0.02 sec)
  7. mysql> create function helloworld () returns int language sql as 'select id from tbl1 limit 1';
  8. Query OK, 0 rows affected (0.02 sec)
  10. mysql> show function status;
  11. +------+------------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
  12. | Db   | Name       | Type     | Definer | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
  13. +------+------------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
  14. | aab  | twosum     | FUNCTION | root    | 2023-03-27 06:25:41 | 2023-03-27 06:25:41 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
  15. | aab  | mysumtable | FUNCTION | root    | 2023-03-27 06:25:51 | 2023-03-27 06:25:51 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
  16. | aab  | helloworld | FUNCTION | root    | 2023-03-27 06:25:58 | 2023-03-27 06:25:58 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
  17. +------+------------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
  18. 3 rows in set (0.00 sec)
  20. mysql> show function status like 'two%';
  21. +------+--------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
  22. | Db   | Name   | Type     | Definer | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
  23. +------+--------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
  24. | aab  | twosum | FUNCTION | root    | 2023-03-27 06:25:41 | 2023-03-27 06:25:41 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
  25. +------+--------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
  26. 1 row in set (0.01 sec)