Menu

2 Ways to List Stored Procedures in MySQL

List MySQL stored procedures with SHOW PROCEDURE STATUS or INFORMATION_SCHEMA.ROUTINES, filter by database, and understand privilege limits.

Posted on By
On this page

Use SHOW PROCEDURE STATUS for a quick list of stored procedures, or query INFORMATION_SCHEMA.ROUTINES when you need to filter or sort the results. Both methods can be scoped to one database. The rows visible to you depend on your MySQL routine privileges. See the MySQL SHOW PROCEDURE STATUS and INFORMATION_SCHEMA.ROUTINES references.

Method Best for
SHOW PROCEDURE STATUS Quickly browsing routines and their metadata
INFORMATION_SCHEMA.ROUTINES Filtering, sorting, or combining routine metadata with other queries

1. Use SHOW PROCEDURE STATUS

List the stored procedures visible to your account:

SHOW PROCEDURE STATUS;

To show procedures in one database, filter its Db column:

SHOW PROCEDURE STATUS
WHERE Db = 'appdb';

The result includes metadata such as database, routine name, definer, and creation or modification time. To look up names matching a pattern, use the LIKE form:

SHOW PROCEDURE STATUS LIKE 'sp_%';

SHOW PROCEDURE STATUS lists stored procedures, not stored functions. MySQL provides a separate SHOW FUNCTION STATUS statement for functions.

2. Query INFORMATION_SCHEMA.ROUTINES

ROUTINES contains rows for both stored procedures and stored functions. Filter ROUTINE_TYPE to list only procedures:

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

Remove the ROUTINE_SCHEMA condition to list procedures from all databases visible to your account. To list only routines in the currently selected database, replace 'appdb' with DATABASE().

You can select other metadata columns, such as DEFINER, SECURITY_TYPE, and ROUTINE_COMMENT. Routine visibility is privilege-dependent: MySQL allows routine metadata access to the definer and to accounts with applicable routine privileges, SHOW_ROUTINE, or global SELECT. If an expected procedure is missing, check the selected database and your grants before assuming it does not exist.

Show a procedure definition

These two listing methods return metadata, not the procedure’s complete SQL body. To inspect one definition, use:

SHOW CREATE PROCEDURE appdb.procedure_name;

You also need sufficient privileges to view the definition. See MySQL stored routine privileges.