In MySQL, stored procedures and their source codes can be viewed for adminsitrational (or other) purposes. To display the characteristics of a stored procedure or stored procedures, we use SHOW PROCEDURE STATUS statement, as shown below:

Syntax

SHOW PROCEDURE STATUS;

This statement produces a list of procedures defined for the database. The list is presented by this table:

Listing Stored Procedures in MySQL
Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation
myDB writers PROCEDURE root@localhost 2015-07-22 18:14:19 2015-07-22 18:10:21 DEFINER   utf8mb4 utf8mb4_general_ci latin1_swedish_ci
myDB articles PROCEDURE root@localhost 2015-07-22 18:16:31 2015-07-22 18:16:31 DEFINER   utf8mb4 utf8mb4_general_ci latin1_swedish_ci

This statement can be used with LIKE or WHERE clauses to filter the results:

SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE expr];

Examples showing how to list stored procedures with name 'email' in it:

SHOW PROCEDURE STATUS LIKE '%email%';

SHOW PROCEDURE STATUS WHERE name LIKE "%email%";

To list the stored procedures with database 'myDB':

SHOW PROCEDURE STATUS WHERE DB = 'rx';

Source code

To display the source code of a stored procedure, we can give that stored procedure its name with SHOW CREATE PROCEDURE clause. The syntax is:

SHOW CREATE PROCEDURE stored_procedure_name;

To view the code of "articles" stored procedure we can create the statement such as:

SHOW CREATE PROCEDURE articles;

This statement will produce the definition of a stored procedure as shown in the table:

Example of stored procedure's code
Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
articles NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` PROCEDURE `articles' utf8mb4 utf8mb4_general_ci latin1_swedish_ci