I've tried to show the SQL code of a stored procedure called add_pixel
:
mysql> SHOW PROCEDURE CODE add_pixel;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
I've made sure that the procedure exists:
mysql> CALL add_pixel();
ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE pixels.add_pixel; expected 3, got 0
And SHOW CREATE PROCEDURE
works:
mysql> SHOW CREATE PROCEDURE add_pixel;
+-----------+----------+------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |
+-----------+----------+------------------+----------------------+----------------------+--------------------+
| add_pixel | | NULL | utf8 | utf8_general_ci | utf8_unicode_ci |
+-----------+----------+------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)
and that my MySQL version supports `SHOW PROCEDURE (SHOW PROCEDURE CODE was added in MySQL 5.0.17).
How do I view the source SQL code of the procedure?
Best Answer
EDIT
Amazingly enough,
INFORMATION_SCHEMA.ROUTINES
does not have a param-list column.You can use
mysql.proc
. For example, looking at a procedure of mine:Look for the
param_list
column (I believe that was your main concern). Also look fordb
,name
,type
.=================
Alternatively, can use
INFORMATION_SCHEMA.ROUTINES
. For example, looking at a procedure of mine:In particular, look at the columns
ROUTINE_SCHEMA
,ROUTINE_NAME
,ROUTINE_TYPE
,ROUTINE_DEFINITION
. These provide most of the interesting data.