{"id":537,"date":"2012-09-14T13:27:58","date_gmt":"2012-09-14T13:27:58","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=537"},"modified":"2023-12-31T02:24:47","modified_gmt":"2023-12-31T09:24:47","slug":"listing-stored-procedures-in-a-mysql-database","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-stored-procedure\/listing-stored-procedures-in-a-mysql-database\/","title":{"rendered":"Listing Stored Procedures"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to list stored procedures from databases in a MySQL server.<\/p>\n\n\n\n

Listing stored procedures using SHOW PROCEDURE STATUS statement<\/h2>\n\n\n\n

Here is the basic syntax of the SHOW PROCEDURE STATUS<\/code> statement:<\/p>\n\n\n

SHOW<\/span> PROCEDURE<\/span> STATUS<\/span> [LIKE<\/span> 'pattern'<\/span> | WHERE<\/span> search_condition]<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The SHOW PROCEDURE STATUS<\/code> statement shows all characteristics of stored procedures including stored procedure names. It returns stored procedures that you have the privilege to access.<\/p>\n\n\n\n
For example, the following statement shows all stored procedures in the current MySQL server:<\/p>\n\n\n
SHOW<\/span> PROCEDURE<\/span> STATUS<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the partial output:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
If you just want to show stored procedures in a particular database, you can use a WHERE<\/code> clause in the  SHOW PROCEDURE STATUS<\/code> as shown in the following statement:<\/p>\n\n\n
SHOW<\/span> PROCEDURE<\/span> STATUS<\/span> \nWHERE<\/span> search_condition;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, this statement lists all stored procedures in the sample database<\/a> classicmodels<\/code>:<\/p>\n\n\n
SHOW<\/span> PROCEDURE<\/span> STATUS<\/span> WHERE<\/span> db = 'classicmodels'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In case you want to find stored procedures whose names contain a specific word, you can use the LIKE<\/code> clause as follows:<\/p>\n\n\n
SHOW<\/span> PROCEDURE<\/span> STATUS<\/span> LIKE<\/span> '%pattern%'<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following statement shows all stored procedures whose names contain the word Order<\/code>:<\/p>\n\n\n
SHOW<\/span> PROCEDURE<\/span> STATUS<\/span> LIKE<\/span> '%Order%'<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"\"<\/figure>\n\n\n\n
Listing stored procedures using the data dictionary<\/h2>\n\n\n\n
The routines<\/code> table in the information_schema<\/code> database contains all information on the stored procedures and stored functions of all databases in the current MySQL server.<\/p>\n\n\n\n
To show all stored procedures of a particular database, you use the following query:<\/p>\n\n\n
SELECT<\/span> \n    routine_name\nFROM<\/span>\n    information_schema.routines\nWHERE<\/span>\n    routine_type = 'PROCEDURE'<\/span>\n        AND<\/span> routine_schema = '<database_name>'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, this statement lists all stored procedures in the classicmodels<\/code> database:<\/p>\n\n\n
SELECT<\/span> \n    routine_name\nFROM<\/span>\n    information_schema.routines\nWHERE<\/span>\n    routine_type = 'PROCEDURE'<\/span>\n        AND<\/span> routine_schema = 'classicmodels'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"\"<\/figure>\n\n\n\n
Showing stored procedures using MySQL Workbench<\/h2>\n\n\n\n
In MySQL Workbench, you can view all stored procedures from a database.<\/p>\n\n\n\n
Step 1. Access the database that you want to view the stored procedures.<\/p>\n\n\n\n
Step 2. Open the Stored Procedures<\/strong> menu. You will see a list of stored procedures that belong to the current database.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
In this tutorial, you have learned how to list the stored procedures in a database by querying them from the data dictionary.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful? <\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t