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.