SHOW CREATE PROCEDURE procedure_name not showing the procedure content

Question

Hello I'm struggling with the Issue where i have to view the stored procedure content for that i have issue the command to get the name of the Procedure

mysql> SHOW PROCEDURE STATUS;
+-----+---------------------------------+-----------+-----------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+-----+---------------------------------+-----------+-----------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| vmp | ccontrol_table | PROCEDURE | user1@% | 2015年11月10日 01:01:51 | 2015年11月10日 01:01:51 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci |
+-----+---------------------------------+-----------+-----------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.27 sec)

It shows me the Procedure Exists with name control_table To view the Procedure i have used SHOW CREATE PROCEDURE control_table it's display in this format not the Content

mysql> SHOW CREATE PROCEDURE control_table;
+---------------------------------+----------+------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |
+---------------------------------+----------+------------------+----------------------+----------------------+--------------------+
| control_table | | NULL | utf8 | utf8_general_ci | latin1_swedish_ci |
+---------------------------------+----------+------------------+----------------------+----------------------+--------------------+
1 row in set (0.28 sec)

I Have executed this Using MySQL Client and mySQL Client Workbench, not able to view the procedure

There is no routine

mysql> SELECT ROUTINE_DEFINITION FROM information_schema.ROUTINES;
+--------------------+
| ROUTINE_DEFINITION |
+--------------------+
| NULL |
+--------------------+
1 row in set (0.28 sec)

The MySQL Version

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+---------------------+
| Variable_name | Value |
+-------------------------+---------------------+
| protocol_version | 10 |
| version | 5.1.73 |
| version_comment | Source distribution |
| version_compile_machine | x86_64 |
| version_compile_os | redhat-linux-gnu |
+-------------------------+---------------------+
5 rows in set (0.26 sec)
mysql> DESC control_table;
ERROR 1146 (42S02): Table 'control_table' doesn't exist

• Rick James: What do you get when you terminate with \G instead of ; in mysql?
• Rick James: SELECT ROUTINE_DEFINITION FROM information_schema.ROUTINES WHERE routine_name = 'control_table'; What version of MySQL?
• anish: @RickJames There is no routine_defintaion
• Rick James: What version of mysql?
• RolandoMySQLDBA: Please run DESC mysql.proc;. There should be a column called 'BODY' which contains the source code of a stored procedure. Do you see such a column ???

Answer 1

It just dawned on me. You must be using MySQL 8.0

I just heard about this headache not too long ago.

The SHOW CREATE PROCEDURE for MySQL 8.0 does not show a body column anymore.

The design of INFORMATION_SCHEMA.ROUTINES is also different for MySQL 8.0.

Someone wrote about this 4 months ago (See No more mysql.proc in MySQL 8.0). This paradigm will take getting used to. Personally, I am not comfortable with it, but we need to just get used to it.

You also gotta wrap your head around INFORMATION_SCHEMA.PARAMETERS, which itemizes each parameter passed to a stored procedure.

Just to skip all this other yucky stuff, MySQL 8.0 FAQ says the following:

Stored procedures are stored in the mysql.routines and mysql.parameters tables, which are part of the data dictionary. You cannot access these tables directly. Instead, query the INFORMATION_SCHEMA ROUTINES and PARAMETERS tables. See Section 25.19, "The INFORMATION_SCHEMA ROUTINES Table", and Section 25.12, "The INFORMATION_SCHEMA PARAMETERS Table".

You can also use SHOW CREATE FUNCTION to obtain information about stored functions, and SHOW CREATE PROCEDURE to obtain information about stored procedures. See Section 13.7.6.9, "SHOW CREATE PROCEDURE Syntax".

I will leave it to you to reserch the rest of this.

• anish: Updated the Question