MySQL Bugs: #10433: INFORMATION_SCHEMA.ROUTINES.ROUTINE

Bug #10433	INFORMATION_SCHEMA.ROUTINES.ROUTINE_DEFINITION incomplete
Submitted:	7 May 2005 10:59	Modified:	2 Jun 2005 10:14
Reporter:	Guy Harrison	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.4	OS:	Windows (Windows XP)
Assigned to:	Sergei Glukhov	CPU Architecture:	Any

Description:
The column ROUTINE_DEFINITION in INFORMATION_SCHEMA.ROUTINES should include the routine header (eg, CREATE statement with parameters) but instead only contains the statement body - at least that is the way it behaves in SQL Server, and without the header you can't use INFORMATION_SCHEMA to emulate SHOW CREATE PROCEDURE.  

How to repeat:
SELECT routine_definition FROM information_schema.routines

Quoting from SQL standard, part 11 (SQL/Schemata):
"
If the SQL-invoked routine being described is an SQL routine, and the
SQL-invoked routine is not contained in an SQL-server module definition,
and the character representation of the <routine body> that defined the
SQL-invoked routine can be represented without truncation, then the
value of ROUTINE_ DEFINITION is that character representation.
"

Quoting from SQL Standard, part 2 (SQL/Foundation):
"
<routine body> ::= <SQL routine spec> | <external body reference>
<SQL routine spec> ::= [ <rights clause> ] <SQL routine body>
<rights clause> ::= SQL SECURITY INVOKER | SQL SECURITY DEFINER
<SQL routine body> ::= <SQL procedure statement>
"

According to the standard document, ROUTINE_DEFINITION must contain
the SQL statement, e.g. "BEGIN ... END" -- not CREATE FUNCTION or
CREATE PROCEDURE, or characteristics.
The workaround is to use SHOW CREATE ROUTINE.