How to view MySQL stored procedure code ?

September 8, 2015 · by · in MySQL

To view procedure code in mysql, you must be the owner of the routine or have SELECT access to the “mysql.proc” table and you can use any of the following statements.

SHOW PROCEDURE CODE proc_name;
SHOW CREATE PROCEDURE proc_name;
SELECT body_utf8 FROM mysql.proc;
If the named routine is available, each statement produces a result set. Each row in the result set corresponds to one “instruction” in the routine.
The first column is Pos, which is an ordinal number beginning with 0.
The second column is Instruction, which contains an SQL statement (usually changed from the original source), or a directive which has meaning only to the stored-routine handler.
[ root @ dbversity : ~ ] mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.25-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>
mysql>
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| test |
+——————–+
7 rows in set (0.02 sec)

mysql>
mysql>
mysql>
mysql>
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>
mysql>
mysql>
mysql> show tables;
+—————————+
| Tables_in_mysql |
+—————————+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+—————————+
28 rows in set (0.00 sec)

mysql>
mysql>
mysql>
mysql> select * from proc;
Empty set (0.02 sec)

mysql>
mysql>
mysql> DELIMITER //
mysql> CREATE PROCEDURE p1 ()
-> BEGIN
-> DECLARE fanta INT DEFAULT 55;
-> DROP TABLE t2;
-> LOOP
-> INSERT INTO t3 VALUES (fanta);
-> END LOOP;
-> END//
Query OK, 0 rows affected (0.05 sec)

mysql>
mysql> DELIMITER ;
mysql>
mysql>
mysql>
mysql> select * from proc;
+——-+——+———–+—————+———-+—————–+——————+—————+————+———+——————————————————————————————————————–+—————-+———————+———————+——————————————–+———+———————-+———————-+——————-+——————————————————————————————————————–+
| db | name | type | specific_name | language | sql_data_access | is_deterministic | security_type | param_list | returns | body | definer | created | modified | sql_mode | comment | character_set_client | collation_connection | db_collation | body_utf8 |
+——-+——+———–+—————+———-+—————–+——————+—————+————+———+——————————————————————————————————————–+—————-+———————+———————+——————————————–+———+———————-+———————-+——————-+——————————————————————————————————————–+
| mysql | p1 | PROCEDURE | p1 | SQL | CONTAINS_SQL | NO | DEFINER | | | BEGIN
DECLARE fanta INT DEFAULT 55;
DROP TABLE t2;
LOOP
INSERT INTO t3 VALUES (fanta);
END LOOP;
END | root@localhost | 2015-09-08 01:23:43 | 2015-09-08 01:23:43 | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | | utf8 | utf8_general_ci | latin1_swedish_ci | BEGIN
DECLARE fanta INT DEFAULT 55;
DROP TABLE t2;
LOOP
INSERT INTO t3 VALUES (fanta);
END LOOP;
END |
+——-+——+———–+—————+———-+—————–+——————+—————+————+———+——————————————————————————————————————–+—————-+———————+———————+——————————————–+———+———————-+———————-+——————-+——————————————————————————————————————–+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> select * from proc\G
*************************** 1. row ***************************
db: mysql
name: p1
type: PROCEDURE
specific_name: p1
language: SQL
sql_data_access: CONTAINS_SQL
is_deterministic: NO
security_type: DEFINER
param_list:
returns:
body: BEGIN
DECLARE fanta INT DEFAULT 55;
DROP TABLE t2;
LOOP
INSERT INTO t3 VALUES (fanta);
END LOOP;
END
definer: root@localhost
created: 2015-09-08 01:23:43
modified: 2015-09-08 01:23:43
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
comment:
character_set_client: utf8
collation_connection: utf8_general_ci
db_collation: latin1_swedish_ci
body_utf8: BEGIN
DECLARE fanta INT DEFAULT 55;
DROP TABLE t2;
LOOP
INSERT INTO t3 VALUES (fanta);
END LOOP;
END
1 row in set (0.00 sec)

mysql>
mysql> select body_utf8 from mysql.proc\G
*************************** 1. row ***************************
body_utf8: BEGIN
DECLARE fanta INT DEFAULT 55;
DROP TABLE t2;
LOOP
INSERT INTO t3 VALUES (fanta);
END LOOP;
END
1 row in set (0.00 sec)

mysql>
mysql>
mysql> select body_utf8 from mysql.proc;
+——————————————————————————————————————–+
| body_utf8 |
+——————————————————————————————————————–+
| BEGIN
DECLARE fanta INT DEFAULT 55;
DROP TABLE t2;
LOOP
INSERT INTO t3 VALUES (fanta);
END LOOP;
END |
+——————————————————————————————————————–+
1 row in set (0.00 sec)

mysql>
mysql> SHOW CREATE PROCEDURE p1\G
*************************** 1. row ***************************
Procedure: p1
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`dbversit_wxpcjyi`@`localhost` PROCEDURE `p1`()
BEGIN
DECLARE fanta INT DEFAULT 55;
DROP TABLE t2;
LOOP
INSERT INTO t3 VALUES (fanta);
END LOOP;
END
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

mysql>
mysql> SHOW PROCEDURE CODE p1//
+—–+—————————————-+
| Pos | Instruction |
+—–+—————————————-+
| 0 | set fanta@0 55 |
| 1 | stmt 9 “DROP TABLE t2” |
| 2 | stmt 5 “INSERT INTO t3 VALUES (fanta)” |
| 3 | jump 2 |
+—–+—————————————-+
4 rows in set (0.00 sec)

  • Ask Question
    • Tags: