Listing Stored Procedures

Listing Stored Procedures in MySQL

In MySQL, you can list all stored procedures available in a database using the SHOW PROCEDURE STATUS statement or by querying the information_schema.ROUTINES table.

1. Using SHOW PROCEDURE STATUS

The SHOW PROCEDURE STATUS command provides a list of all stored procedures in the MySQL server.

Syntax

SHOW PROCEDURE STATUS;

Filtering by Database

To list stored procedures for a specific database, use the LIKE clause:

SHOW PROCEDURE STATUS WHERE Db = 'your_database_name';

📌 Example

SHOW PROCEDURE STATUS WHERE Db = 'mydb';

✔ This will show only stored procedures that belong to the mydb database.

2. Using information_schema.ROUTINES

You can also retrieve a list of stored procedures using the information_schema.ROUTINES table.

Query to List Stored Procedures

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

📌 Example

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

✔ This query returns the stored procedure name, creation date, and last modified date.

3. Viewing the Definition of a Stored Procedure

Once you have a stored procedure name, you can view its definition using:

Using SHOW CREATE PROCEDURE

SHOW CREATE PROCEDURE your_database_name.procedure_name;

📌 Example

SHOW CREATE PROCEDURE mydb.get_users;

✔ This displays the full SQL code of the get_users stored procedure.

4. Deleting a Stored Procedure

If you want to remove a stored procedure, use:

DROP PROCEDURE IF EXISTS procedure_name;

📌 Example

DROP PROCEDURE IF EXISTS get_users;

✔ This ensures the procedure is removed only if it exists, preventing errors.

5. Key Takeaways

✔ Use SHOW PROCEDURE STATUS to list all stored procedures.
✔ Query information_schema.ROUTINES for more details.
✔ Use SHOW CREATE PROCEDURE to view the definition of a procedure.
✔ Drop unwanted procedures using DROP PROCEDURE.

Would you like an example of creating and managing stored procedures? 🚀