JIYIK CN >

This article introduces three ways to list all stored procedures in MySQL. They include the SHOW PROCEDURE STATUS statement, the data directory, and MySQL Workbench (a visual tool).

List all stored procedures in MySQL

We will use the data directory, MySQL Workbench, and the SHOW PROCEDURE STATUS statement to list all stored procedures in MySQL. All queries given in this article can be executed in the Windows command line once logged into the MySQL server.

We will use MySQL Workbench, a visual tool, to perform queries in this article as it displays large data in a proper tabular form for easy understanding.

Use the SHOW PROCEDURE STATUS statement to list all stored procedures in MySQL.

SHOW PROCEDURE STATUSThe basic syntax of the statement is as follows.

SHOW PROCEDURE STATUS [LIKE 'yourPattern' | WHERE searchCondition]

The following command displays all the characteristics of a stored procedure, including the database name. It also contains the procedure name, creation and modification date, description, etc.

It returns all stored procedures in the current MySQL server that we have privileges to access.

SHOW PROCEDURE STATUS;

Output:

Use the WHERE clause to list all stored procedures in a specific database

We can use the WHERE clause with the SHOW PROCEDURE STATUS statement to get all the stored procedures in a particular database. See the following query as an example.

SHOW PROCEDURE STATUS WHERE db = 'test';

Output:

Use the LIKE operator to list all stored procedures that contain a specific pattern

We can search for all stored procedures with a specific pattern in the procedure name. Wildcards are useful for writing patterns.

A sample query is given below.

SHOW PROCEDURE STATUS LIKE '%perform%'

Output:

List all stored procedures in MySQL using the data directory

Another way to list all stored procedures is to query the routines table of the information_schema database. The routines table contains all the details about the stored functions and procedures of all the databases on the current MySQL server.

Here we can list all stored procedures for all databases.

SELECT  routine_name FROM information_schema.routines
WHERE routine_type = 'PROCEDURE';

Output:

We can use the following query to list all the stored procedures for a particular database using the routines table.

SELECT routine_schema, routine_name
FROM information_schema.routines
WHERE routine_type = 'PROCEDURE'
AND routine_schema = 'test';

List all stored procedures in MySQL using MySQL Workbench

We can click on the Stored Procedures option to list all stored procedures for each database individually.