2 Ways to List all Functions in MySQL

Below are two options we can use to return a list of functions in MySQL.

The SHOW FUNCTION STATUS Command

The quickest way to list out all functions is to use the SHOW FUNCTION STATUS command.

Run the following to list out all functions:

SHOW FUNCTION STATUS;

The syntax goes like this:

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

So you can use a LIKE or WHERE clause to narrow down the results.

Example:

SHOW FUNCTION STATUS LIKE '%test%';

Example result:

+----------+---------------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db       | Name          | Type     | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+----------+---------------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| PetHotel | test_function | FUNCTION | root@localhost | 2021-11-29 08:09:26 | 2021-11-29 08:09:26 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+----------+---------------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+

We can use the WHERE clause to filter the results by the columns. For example, we can filter the results to a given database:

SHOW FUNCTION STATUS WHERE db = 'sakila';

Example result:

+--------+----------------------------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db     | Name                       | Type     | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+--------+----------------------------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| sakila | get_customer_balance       | FUNCTION | root@localhost | 2021-11-29 08:04:31 | 2021-11-29 08:04:31 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
| sakila | inventory_held_by_customer | FUNCTION | root@localhost | 2021-11-29 08:04:31 | 2021-11-29 08:04:31 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
| sakila | inventory_in_stock         | FUNCTION | root@localhost | 2021-11-29 08:04:31 | 2021-11-29 08:04:31 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+--------+----------------------------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+

The SHOW FUNCTION STATUS command works very much like the SHOW PROCEDURE STATUS command, which returns a list of stored procedures.

The information_schema.routines Table

Another way to get a list of functions in MySQL is to query the information_schema.routines table.

Example:

SELECT 
    routine_schema as "Database",
    routine_name
FROM 
    information_schema.routines
WHERE 
    routine_type = 'FUNCTION'
ORDER BY 
    routine_schema ASC, 
    routine_name ASC;

This table also stores information about stored procedures. In the above example, I excluded those by using a WHERE clause to return only functions (i.e. objects with a routine_type of FUNCTION).

To include stored procedures we can remove the WHERE clause:

SELECT 
    routine_schema as "Database",
    routine_name,
    routine_type
FROM 
    information_schema.routines
ORDER BY 
    routine_schema ASC, 
    routine_name ASC;

In this case I also added the routine_type column so that we can distinguish between the procedures and functions.

We can also narrow it down to a specific database:

SELECT 
    routine_schema as "Database",
    routine_name,
    routine_type
FROM 
    information_schema.routines
WHERE 
    routine_schema = 'pethotel'
ORDER BY 
    routine_name ASC;

Sample result:

+----------+---------------+--------------+
| Database | ROUTINE_NAME  | ROUTINE_TYPE |
+----------+---------------+--------------+
| PetHotel | spGetAllPets  | PROCEDURE    |
| PetHotel | spGetPetById  | PROCEDURE    |
| PetHotel | test_function | FUNCTION     |
+----------+---------------+--------------+