Listing Stored Functions

Listing Stored Functions in MySQL

In MySQL, stored functions are user-defined functions that return a single value. If you need to list all stored functions in a MySQL database, you can retrieve them from the information_schema database.

1. Using `SHOW FUNCTION STATUS`

The SHOW FUNCTION STATUS statement details all stored functions, including their name, database, creation time, and security type.

Syntax

SHOW FUNCTION STATUS;

✅ This returns all functions across all databases.

Filtering by a Specific Database

SHOW FUNCTION STATUS WHERE Db = 'your_database_name';

✅ This returns functions specific to your_database_name.

2. Querying `information_schema.ROUTINES`

The ROUTINES table in information_schema stores details about both stored procedures and stored functions.

Syntax

SELECT ROUTINE_NAME, ROUTINE_TYPE, CREATED, DEFINER 
FROM information_schema.ROUTINES
WHERE ROUTINE_TYPE = 'FUNCTION' AND ROUTINE_SCHEMA = 'your_database_name';

✅ This retrieves only stored functions in the specified database.

3. Listing Functions with Specific Characteristics

You can customize queries to filter stored functions based on different criteria:

a) Functions Created by a Specific User

SELECT ROUTINE_NAME, DEFINER
FROM information_schema.ROUTINES
WHERE ROUTINE_TYPE = 'FUNCTION' AND DEFINER LIKE '%root%';

✅ Returns all functions created by root.

b) Functions Created After a Certain Date

SELECT ROUTINE_NAME, CREATED
FROM information_schema.ROUTINES
WHERE ROUTINE_TYPE = 'FUNCTION' AND CREATED > '2024-01-01';

✅ Lists functions created after January 1, 2024.

4. Checking Function Definitions

To view the SQL definition of a function:

SHOW CREATE FUNCTION your_function_name;

✅ This returns the complete function definition, including the body, parameters, and return type.

Summary

✔ SHOW FUNCTION STATUS – Lists all stored functions.
✔ information_schema.ROUTINES – Allows filtering and detailed queries.
✔ SHOW CREATE FUNCTION – Displays the exact function definition.
✔ Filtering options – Retrieve functions based on database, creator, or creation date.

Would you like a sample function creation and listing example? 🚀

Souy Soeng

Listing Stored Functions