How to list mysql stored procedure parameters

Question

What is the SQL to list the parameters of a MySQL stored procdure? The information_schema.routines table holds the stored procedure names but there doesn't seem to be a standard place where the parameters are stored.

Answer 1

More recent versions of MySQL (5.5.3 and above) introduced the information_schema.parameters object which should give you the information you need;

SELECT * 
FROM information_schema.parameters 
WHERE SPECIFIC_NAME = 'your_procedure';

Earlier versions of MySql rely on having access to the mysql.proc table; the column 'param_list' has all of the parameter information in there for the procedure with the name you are interested in. The information is decidedly non-normalised, though, as it is stored as comma separated string:

SELECT param_list FROM mysql.proc WHERE db='your_database' AND name='your_procedure';

Gives:

IN param1 VARCHAR(32), IN param2 int, ... 

This requires some more work to put into a format for presentation; although a string.split function would at least tidy it up.

Answer 2

show create procedure show create function? these inbound calls require the access to the mysql.proc table that's where you have to look at and the body field is of longblob type there select cast(param_list as char) from mysql.proc where name='' and type='PROCEDURE'