How to Show Stored Procedures in MySQL Easily

To show stored procedures in MySQL, use the SHOW PROCEDURE STATUS command to list all procedures. You can also query the information_schema.ROUTINES table filtering by ROUTINE_TYPE = 'PROCEDURE' to get detailed information.

Syntax

The main ways to show stored procedures in MySQL are:

• SHOW PROCEDURE STATUS [LIKE 'pattern']; - Lists stored procedures with optional name filtering.
• SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_SCHEMA = 'your_database'; - Queries detailed info about procedures in a specific database.

SHOW PROCEDURE STATUS shows basic info like procedure name, database, creation date.

information_schema.ROUTINES gives more details like parameters, definition, and type.

sql

SHOW PROCEDURE STATUS [LIKE 'pattern'];

SELECT * FROM information_schema.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_SCHEMA = 'your_database';

Example

This example shows how to list all stored procedures in the current database named testdb using both methods.

sql

USE testdb;

-- List all stored procedures with SHOW PROCEDURE STATUS
SHOW PROCEDURE STATUS WHERE Db = 'testdb';

-- List all stored procedures with detailed info
SELECT ROUTINE_NAME, CREATED, LAST_ALTERED
FROM information_schema.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_SCHEMA = 'testdb';

Output

SHOW PROCEDURE STATUS WHERE Db = 'testdb';
+---------+--------+----------+---------------------+---------------------+---------------------+----------------+---------+---------------------+---------------------+
| Db      | Name   | Type     | Definer             | Modified            | Created             | Security_type  | Comment | character_set_client | collation_connection |
+---------+--------+----------+---------------------+---------------------+---------------------+----------------+---------+---------------------+---------------------+
| testdb  | sp_add | PROCEDURE| root@localhost      | 2024-06-01 10:00:00 | 2024-06-01 09:50:00 | DEFINER        |         | utf8mb4             | utf8mb4_general_ci   |
+---------+--------+----------+---------------------+---------------------+---------------------+----------------+---------+---------------------+---------------------+

SELECT ROUTINE_NAME, CREATED, LAST_ALTERED
FROM information_schema.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_SCHEMA = 'testdb';
+--------------+---------------------+---------------------+
| ROUTINE_NAME | CREATED             | LAST_ALTERED        |
+--------------+---------------------+---------------------+
| sp_add       | 2024-06-01 09:50:00 | 2024-06-01 10:00:00 |
+--------------+---------------------+---------------------+

Common Pitfalls

Common mistakes when trying to show stored procedures include:

• Not specifying the correct database in SHOW PROCEDURE STATUS or information_schema.ROUTINES, which may show procedures from other databases or none.
• Confusing stored procedures with stored functions; use ROUTINE_TYPE = 'PROCEDURE' to filter only procedures.
• Trying to use SHOW PROCEDURES which is not a valid command in MySQL.

sql

/* Wrong: SHOW PROCEDURES is invalid */
SHOW PROCEDURES;

/* Right: Use SHOW PROCEDURE STATUS */
SHOW PROCEDURE STATUS WHERE Db = 'testdb';

Quick Reference

Summary of commands to show stored procedures in MySQL:

Command	Description
SHOW PROCEDURE STATUS [LIKE 'pattern'];	Lists stored procedures with optional name filter.
SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_SCHEMA = 'db_name';	Shows detailed info about procedures in a database.
SHOW PROCEDURES;	Invalid command, do not use.

Key Takeaways

Use SHOW PROCEDURE STATUS to list stored procedures in MySQL.

Filter by database name to see procedures only in your target database.

Query information_schema.ROUTINES for detailed procedure information.

Do not use SHOW PROCEDURES; it is not a valid MySQL command.

Remember to filter by ROUTINE_TYPE = 'PROCEDURE' to exclude functions.