One of my site uses one big procedure to log all access statistics but for some very stupid reason, I didn't put that procedure anywhere in my version copies. Talk about stupid. But then the question rose, how the hell do I retrieve it?
When googling for something like "retrieve procedure from database" and mysql and so forth, all I got was SHOW PROCEDURE CODE, which wasn't even working on my version (5.0.something). Closest I had gotten was a comment on mysql retrieving information from the INFORMATION_SCHEMA. But just the name. But I knew the name...
So I was about to give up on it and rewrite the whole thing when I figured, hey, that's one field, what would * produce. And yes, woohoo, there's my procedure! Well, mainly.
So, you'll need the proper access rights for this or this will undoubtfully not work. But to retrieve a defined procedure (or function) from the database, here's what you do:
SELECT ROUTINE_DEFINITION,ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='PROCEDURE'
Remember that you can replace
SELECT ROUTINE_DEFINITION,ROUTINE_NAME by * to get a full dump.
A small PHP script, which also sets up the output a little, would look like this:
$query = "
SELECT ROUTINE_DEFINITION,ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='PROCEDURE'
";
$result = squery($query);
if (mysqlerror()) return;
echo "";
while ($row = mysql_fetch_assoc($result)) {
var_dump($row);
}
echo "
";
where squery() is my function wrapper for mysql_query() that deals with errors and reporting and mysqlerror() returns true if it did error out (the message would have been displayed by then).
The result is the inner body of the procedure. It misses the variables and arguments, but at least it's something.
Hope it helps you.