How to list stored procedures and show procedure code in MySQL

To list the stored procedures in MySQL and display their procedure code, follow these steps:

Listing MySQL stored procedures

• List MySQL stored procedures in the system: mysql> SHOW PROCEDURE STATUS Output:

  +------+-----------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
  | Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
  +------+---------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
  | mydb | mysql_proc | PROCEDURE | root@% | 2013-08-18 20:29:53 | 2013-08-18 20:29:53 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci |
  | test | test_proc  | PROCEDURE | test@% | 2013-09-10 20:09:53 | 2013-09-10 20:09:53 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci |
  +------+------------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+

• List MySQL stored procedures in a particular database using where: mysql> SHOW PROCEDURE STATUS WHERE Db = 'test' Output:

  +------+-----------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
  | Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
  +------+---------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
  | test | test_proc | PROCEDURE | test@% | 2013-09-10 20:09:53 | 2013-09-10 20:09:53 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci |
  +------+------------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+

Displaying a stored procedure’s code

From the stored procedure listing, we can display the code for test_proc in the test database as follows:

1. Enter the database containing the stored procedure whose code you want to display. mysql> use test;

2. Run SHOW CREATE PROCEDURE to display its code, as shown below: mysql> SHOW CREATE PROCEDURE test_proc\G; Output:

   *************************** 1. row ***************************
   Procedure: test_proc
   sql_mode:
   Create Procedure: CREATE DEFINER=`test`@ PROCEDURE `test_proc`(pCODE Varchar(11))
   BEGIN
   IF (SELECT EXISTS(SELECT 1 FROM t WHERE MSISDN = pCODE LIMIT 1) IsExist)
   THEN
   SELECT 'IsExist';
   ELSE
   SELECT '!IsExist';
   END IF;
   END
   character_set_client: latin1
   collation_connection: latin1_swedish_ci
   Database Collation: latin1_swedish_ci