MySQL Tutorial/Data Dictionary/routines

Check information_schema.routines

   <source lang="sql">

mysql> mysql> mysql> DELIMITER $$ mysql> mysql> CREATE PROCEDURE myProc(

   ->   in_start_date DATE,
   ->   in_end_date   DATE)
   ->     DETERMINISTIC
   -> BEGIN
   ->   DECLARE l_first_name,l_last_name VARCHAR(60);
   ->   DECLARE l_id INT DEFAULT 0;
   ->
   ->   SELECT id,first_name,last_name
   ->         FROM employee
   ->        WHERE start_date BETWEEN in_start_date AND in_end_date;
   ->
   -> END $$

Query OK, 0 rows affected (0.00 sec) mysql> mysql> DELIMITER ; mysql> SELECT CONCAT(routine_schema,".",routine_name),

   ->        routine_type,routine_definition
   ->   FROM information_schema.routines;

+-----------------------------------------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | CONCAT(routine_schema,".",routine_name) | routine_type | routine_definition | +-----------------------------------------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test.myProc | PROCEDURE | BEGIN

 DECLARE l_first_name,l_last_name VARCHAR(60);
 DECLARE l_id INT DEFAULT 0;
 SELECT id,first_name,last_name
       FROM employee
      WHERE start_date BETWEEN in_start_date AND in_end_date;

END | | test.simpleproc | PROCEDURE | BEGIN

 SELECT COUNT(*) INTO param1 FROM employee;

END | +-----------------------------------------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.01 sec) mysql> mysql> drop PROCEDURE myProc; Query OK, 0 rows affected (0.00 sec)</source>