Apa itu routines mysql database

Summary: in this tutorial, you will learn how to list stored procedures from databases in a MySQL Server.

Listing stored procedures using SHOW PROCEDURE STATUS statement

Here is the basic syntax of the SHOW PROCEDURE STATUS statement:

SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE search_condition]
Code language: SQL (Structured Query Language) (sql)

The SHOW PROCEDURE STATUS statement shows all characteristic of stored procedures including stored procedure names. It returns stored procedures that you have a privilege to access.

The following statement shows all stored procedure in the current MySQL server:

SHOW PROCEDURE STATUS;
Code language: SQL (Structured Query Language) (sql)

Here is the partial output:

If you just want to show stored procedures in a particular database, you can use a WHERE clause in the  SHOW PROCEDURE STATUS as shown in the following statement:

SHOW PROCEDURE STATUS WHERE search_condition;
Code language: SQL (Structured Query Language) (sql)

For example, this statement lists all stored procedures in the sample database classicmodels:

SHOW PROCEDURE STATUS WHERE db = 'classicmodels';
Code language: SQL (Structured Query Language) (sql)

In case you want to find stored procedures whose names contain a specific word, you can use the LIKE clause as follows:

SHOW PROCEDURE STATUS LIKE '%pattern%'
Code language: SQL (Structured Query Language) (sql)

The following statement shows all stored procedure whose names contain the wordOrder:

SHOW PROCEDURE STATUS LIKE '%Order%'



Code language: SQL (Structured Query Language) (sql)

Listing stored procedures using the data dictionary

The routines table in the information_schema database contains all information on the stored procedures and stored functions of all databases in the current MySQL server.

To show all stored procedures of a particular database, you use the following query:

SELECT 
    routine_name
FROM
    information_schema.routines
WHERE
    routine_type = 'PROCEDURE'
        AND routine_schema = '<database_name>';
Code language: SQL (Structured Query Language) (sql)

For example, this statement lists all stored procedures in the classicmodels database:

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



Code language: SQL (Structured Query Language) (sql)

Showing stored procedures using MySQL Workbench

In MySQL Workbench, you can view all stored procedures from a database.

Step 1. Access the database that you want to view the stored procedures.

Step 2. Open the Stored Procedures menu. You will see a list of stored procedures that belong to the current database.

In this tutorial, you have learned how to list the stored procedures in a database by querying them from the data dictionary.

Was this tutorial helpful?