briankip briankip - 26 days ago 9
MySQL Question

Store the result of `show create procedure procedure_name` into a variable in MySql

I want to get the code used to create an existing procedure by using the command

show create procedure
and then store it in a variable. All this inside a procedure.

Problem




  • Set @var
    for creating user defined variables only works when the result contains one row and one column.

  • show create procedure proc_name
    returns multiple columns and I cant select the specific one which is
    Create Function



Question



How can I save the code for creating a procedure into a variable for later use.

Answer

You can use the routines table in the information_schema database.

select
routine_definition
from
information_schema.routines
where
specific_name = 'nameOfYourStoredProcedure'

For more information see this link.

UPDATE:

Then you will have to build it yourself. There's no other way.

select
CONCAT('CREATE PROCEDURE nameOfYourStoredProcedure ', routine_definition, 'END') /*something like that, this here is pseudo-code*/
from
information_schema.routines
where
specific_name = 'nameOfYourStoredProcedure'

UPDATE 2:

Considering Devart's comment, you will have to also use the parameters table from information_schema database.

Note: This table was added with MySQL 5.5.3

For more information have a look here.

I honestly don't know of any other way to solve this. Also I can't update to 5.5.3 here to help you. Good luck.

Comments