You can view the list of functions created in your MySQL database server by using the SHOW FUNCTION STATUS statement.
The SHOW FUNCTION STATUS statement will show all functions available in your database server as shown below:
SHOW FUNCTION STATUS;
The output would look as follows. Note that some columns are omitted for clarity:
+-----------+----------------------------------+----------+---------------------+
| Db | Name | Type | Definer |
+-----------+----------------------------------+----------+---------------------+
| school_db | calculateScore | FUNCTION | root@localhost |
| school_db | hello | FUNCTION | root@localhost |
| sys | extract_schema_from_file_name | FUNCTION | mysql.sys@localhost |
+-----------+----------------------------------+----------+---------------------+
To narrow down the result of the SHOW FUNCTION STATUS statement, you can use a WHERE or LIKE clause.
The following query will return functions stored in the school_db database only:
SHOW FUNCTION STATUS
WHERE db='school_db';
The returned result set will be as follows:
+-----------+----------------+----------+----------------+
| Db | Name | Type | Definer |
+-----------+----------------+----------+----------------+
| school_db | calculateScore | FUNCTION | root@localhost |
| school_db | hello | FUNCTION | root@localhost |
+-----------+----------------+----------+----------------+
You can also filter the result using function names with the LIKE clause:
SHOW FUNCTION STATUS
LIKE 'hello';
Because I have a function named hello, the returned result set from my computer is as follows:
+-----------+-------+----------+----------------+
| Db | Name | Type | Definer |
+-----------+-------+----------+----------------+
| school_db | hello | FUNCTION | root@localhost |
+-----------+-------+----------+----------------+
You can use the wild card % symbol to find all functions with names that match the pattern like this:
SHOW FUNCTION STATUS
LIKE '%hello%';
Finally, if you want to see the code inside the function, you can use the SHOW CREATE FUNCTION statement followed by the function name.
SHOW CREATE FUNCTION [function name]
When I run the following query:
SHOW CREATE FUNCTION hello \G
The result is as follows:
SHOW CREATE FUNCTION hello \G
*************************** 1. row ***************************
Function: hello
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,
NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `hello`(
input1 VARCHAR(10)
) RETURNS varchar(50) CHARSET utf8mb4
DETERMINISTIC
RETURN "Hello World"
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
Database Collation: utf8mb4_0900_ai_ci
And that’s how you can show functions available in your MySQL database server 😉