Listing Stored Procedures

Listing Stored Procedures in MySQL

To list all stored procedures in a MySQL database, you can query the INFORMATION_SCHEMA.ROUTINES table, which stores metadata about stored procedures and functions.

Query to List Stored Procedures

SELECT ROUTINE_NAME, ROUTINE_TYPE, CREATED, LAST_ALTERED 
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
  AND ROUTINE_SCHEMA = 'your_database_name';

Explanation:

ROUTINE_NAME: The name of the stored procedure.
ROUTINE_TYPE: Type of the routine (always PROCEDURE for stored procedures).
CREATED: The date and time when the procedure was created.
LAST_ALTERED: The date and time of the last modification.
ROUTINE_SCHEMA: The database where the procedure exists.

Example

Suppose you have a database named company with stored procedures. To list all procedures, run:

SELECT ROUTINE_NAME, CREATED, LAST_ALTERED
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
  AND ROUTINE_SCHEMA = 'company';

Sample Output:

+------------------+---------------------+---------------------+
| ROUTINE_NAME     | CREATED             | LAST_ALTERED        |
+------------------+---------------------+---------------------+
| add_employee     | 2025-01-20 15:30:00 | 2025-01-22 12:00:00 |
| delete_employee  | 2025-01-18 10:45:00 | 2025-01-18 10:45:00 |
+------------------+---------------------+---------------------+

Alternative: SHOW PROCEDURE STATUS

You can also use the SHOW PROCEDURE STATUS command to list stored procedures.

Query:

SHOW PROCEDURE STATUS WHERE Db = 'your_database_name';

Explanation:

SHOW PROCEDURE STATUS: Lists all stored procedures in the server.
Db: Filters results for a specific database.

Output Example:

+------------------+-------+-----------+---------------------+---------------------+
| Name             | Db    | Type      | Created             | Modified            |
+------------------+-------+-----------+---------------------+---------------------+
| add_employee     | company | PROCEDURE | 2025-01-20 15:30:00 | 2025-01-22 12:00:00 |
| delete_employee  | company | PROCEDURE | 2025-01-18 10:45:00 | 2025-01-18 10:45:00 |
+------------------+-------+-----------+---------------------+---------------------+

Useful Filters

1. List Procedures Created After a Certain Date

SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
  AND ROUTINE_SCHEMA = 'your_database_name'
  AND CREATED > '2025-01-01';

2. Search for a Specific Procedure

SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
  AND ROUTINE_SCHEMA = 'your_database_name'
  AND ROUTINE_NAME LIKE '%employee%';

Key Notes

Access Privileges: You need appropriate privileges to view stored procedures.
Routine Types: MySQL distinguishes between PROCEDURE and FUNCTION. Use ROUTINE_TYPE to filter.
Case Sensitivity: Routine names are case-sensitive depending on the underlying file system.

Conclusion

MySQL provides multiple ways to list stored procedures, such as querying the INFORMATION_SCHEMA.ROUTINES table or using the SHOW PROCEDURE STATUS command. These methods help administrators and developers manage stored procedures effectively in a database.

Souy Soeng

Listing Stored Procedures