Listing Stored Functions

Summary: in this tutorial, you will learn how to show stored functions from databases by using the SHOW FUNCTION STATUS or querying the data dictionary.

Listing stored functions using SHOW FUNCTION STATUS statement

The SHOW FUNCTION STATUS is like the SHOW PROCEDURE STATUS but for the stored functions.

Here is the basic syntax of the SHOW FUNCTION STATUS statement:

SHOW FUNCTION STATUS 
[LIKE 'pattern' | WHERE search_condition];Code language: SQL (Structured Query Language) (sql)

The SHOW FUNCTION STATUS statement returns all characteristics of stored functions. The following statement shows all stored functions in the current MySQL server:

SHOW FUNCTION STATUS;Code language: SQL (Structured Query Language) (sql)

Note that the SHOW FUNCTION STATUS only shows the function that you have a privilege to access.

If you just want to show stored functions in a particular database, you can use a WHERE clause in the  SHOW FUNCTION STATUS as shown in the following statement:

SHOW FUNCTION STATUS 
WHERE search_condition;Code language: SQL (Structured Query Language) (sql)

For example, this statement shows all stored functions in the sample database classicmodels:

SHOW FUNCTION STATUS 
WHERE db = 'classicmodels';Code language: SQL (Structured Query Language) (sql)

If you want to find the stored functions whose names contain a specific word, you can use the LIKE clause:

SHOW FUNCTION STATUS 
LIKE '%pattern%';Code language: SQL (Structured Query Language) (sql)

The following statement shows all stored functions whose names contain the word Customer:

SHOW FUNCTION STATUS LIKE '%Customer%';Code language: SQL (Structured Query Language) (sql)

Listing stored functions using the data dictionary

MySQL data dictionary has a routines table that stores information about the stored functions of all databases in the current MySQL server.

This query finds all stored functions in a particular database:

SELECT 
    routine_name
FROM
    information_schema.routines
WHERE
    routine_type = 'FUNCTION'
        AND routine_schema = '<database_name>';Code language: SQL (Structured Query Language) (sql)

For example, the following statement returns all stored functions in the classicmodels database:

SELECT 
    routine_name
FROM
    information_schema.routines
WHERE
    routine_type = 'FUNCTION'
        AND routine_schema = 'classicmodels';Code language: SQL (Structured Query Language) (sql)

Showing stored functions using MySQL Workbench

If you use MySQL Workbench, you can view all stored functions from a database.

Step 1. Connect to the database that you want to show the stored functions.

Step 2. Open the Functions menu, you will see a list of functions which belong to the database.

In this tutorial, you have learned how to show stored functions in a database by using the SHOW FUNCTION STATUS statement and querying from the data dictionary.