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];
The SHOW FUNCTION STATUS
the statement returns all characteristics of stored functions. The following statement shows all stored functions in the current MySQL server:
SHOW FUNCTION STATUS;
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;
For example, this statement shows all stored functions in the sample database classicmodels
:
SHOW FUNCTION STATUS
WHERE db = 'classicmodels';
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%';
The following statement shows all stored functions whose names contain the word Customer
:
SHOW FUNCTION STATUS LIKE '%Customer%';
Listing stored functions using the data dictionary
MySQL data dictionary has a routines
the 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>';
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';
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 that 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.
0 Comments
CAN FEEDBACK
Emoji