Listing Stored Procedures

Master listing MySQL stored procedures — SHOW PROCEDURE STATUS with LIKE and WHERE clauses, querying information_schema.ROUTINES with proper routine_type and routine_schema filters, viewing routine_definition for source code, and using MySQL Workbench for visual browsing.

On a real system you'll often need to discover what stored procedures exist — to audit a database, find a procedure by name pattern, see who created it, or check when it was last modified. MySQL gives you three tools:

Returns one row per procedure that the current user has permission to see, with these columns: Db, Name, Type, Definer, Modified, Created, Security_type, Comment, character_set_client, collation_connection, Database Collation.

SHOW PROCEDURE STATUS;

To narrow the listing to procedures in one specific database, add a WHERE clause:

SHOW PROCEDURE STATUS WHERE Db = 'gowtham';

You can use any SQL boolean expression in WHERE — combine conditions, filter by Definer, by Created date, etc.

Use LIKE to find procedures whose name matches a pattern:

SHOW PROCEDURE STATUS LIKE '%emp%';

The information_schema database is MySQL's built-in metadata catalog. The ROUTINES table contains one row per stored procedure or function across all databases. Querying it programmatically is more powerful than SHOW PROCEDURE STATUS — you can join, filter, sort, and project arbitrary columns.

Find every procedure (not function) in the gowtham database:

SELECT routine_name
FROM   information_schema.ROUTINES
WHERE  routine_type   = 'PROCEDURE'
  AND  routine_schema = 'gowtham';

SELECT routine_name FROM routine_name
WHERE routine_name='PROCEDURE'
  AND routine_name='gowtham'

The full ROUTINES table has dozens of columns. A practical query selecting the most useful ones:

SELECT routine_schema   AS db,
       routine_name     AS name,
       routine_type     AS type,
       data_access      AS sql_data_access,
       security_type,
       definer,
       created,
       last_altered,
       routine_comment  AS comment
FROM   information_schema.ROUTINES
WHERE  routine_schema = 'gowtham'
ORDER  BY routine_name;

The routine_definition column contains the procedure body itself:

SELECT routine_name, routine_definition
FROM   information_schema.ROUTINES
WHERE  routine_type   = 'PROCEDURE'
  AND  routine_schema = 'gowtham'
  AND  routine_name   = 'GetTotalEmp'\G

For visual exploration, MySQL Workbench is the easiest route:

1. Open the schema panel on the left.
2. Expand the database you're interested in.
3. Expand the Stored Procedures node — every procedure in that database is listed.
4. Right-click any procedure for options: alter, drop, view definition, copy to clipboard.

DBeaver, HeidiSQL, and other GUI clients have similar trees. They all read from information_schema under the hood.

1. Start with SHOW PROCEDURE STATUS for ad-hoc browsing — fastest to type and gives the most-needed columns.
2. Reach for information_schema.ROUTINES when you need joins, custom output, or programmatic access.
3. Always filter by routine_schema when querying ROUTINES — without it, you'll get every procedure on the server, including system schemas.
4. Specify routine_type too — the same table holds both procedures and functions.
5. Use SHOW CREATE PROCEDURE name\G when you need the full DDL with parameters and characteristics.

• Three ways to find stored procedures: SHOW PROCEDURE STATUS, information_schema.ROUTINES, and GUI clients like MySQL Workbench.
• SHOW PROCEDURE STATUS accepts LIKE 'pattern' for name matching and WHERE condition for arbitrary filtering.
• information_schema.ROUTINES contains both procedures and functions — always filter by routine_type = 'PROCEDURE' and routine_schema = 'your_db'.
• The routine_definition column holds just the body between BEGIN and END; use SHOW CREATE PROCEDURE name\G for the complete CREATE statement.
• For multi-database scripting, querying information_schema.ROUTINES is more flexible than SHOW PROCEDURE STATUS.