How to list stored procedures and functions in MySQL database
How to list stored procedures and functions in MySQL database
Feb 07th, 2022,In this article, we will show you how to list stored procedures and functions in a MySQL database.
1. Using SQL Query
The following SQL script should return a list of all stored procedures and functions of the MySQL database.
SELECT routine_schema,
routine_name,
routine_type
FROM information_schema.routines
WHERE routine_schema = 'your_database_name'
ORDER BY routine_name;
Sample results
The result of the given query will list stored procedures and functions in the MySQL database:
2. Using MySQL interactive shell command-line
If you are using the MySQL shell command line you can list all procedures in the MySQL database with the SHOW PROCEDURE STATUS WHERE db = 'your_database_name'; command.
The output of the show procedure command will be:
Listing all functions in a MySQL database can be done using SHOW FUNCTION STATUS WHERE db = 'your_database_name'; command.
The output of the command:
3. List stored procedures and functions with ERBuilder Data Modeler
ERBuilder Data Modeler allows you to visualize the structure of your MySQL database as well as the visualization for the list of all stored procedures and functions in your database.
For more details such as the stored procedure statement or the function statement ERBuilder Data Modeler allows you to visualize that just by double-clicking on the procedure name or the function name, furthermore, it allows you to edit it delete it, or add new functions or procedures.
Start modeling your database with us today
Subscribe To Our Newsletter
Subscribe to our email newsletter today to receive updates of the latest news, tutorials and special offers!