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:Microsoft Windows (Windows XP)
Assigned to: Sergei Glukhov CPU Architecture:Any

[7 May 2005 10:59] Guy Harrison
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
[2 Jun 2005 10:14] Sergei Glukhov
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.