6

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.

icc97
  • 11,395
  • 8
  • 76
  • 90
  • See http://stackoverflow.com/questions/733349/list-of-stored-procedures-functions-mysql-command-line. Not sure you can isolate the parameters without the body text – kaj Mar 03 '12 at 13:16
  • 2
    Which version are you using? Newer versions have information_schema.parameters. In older versions, look at mysql.proc - column paramlist (although they aren't in a great format!) – dash Mar 03 '12 at 13:18
  • @dash bang on, don't know how I missed that table. I'm using 5.5.9. If you put that as an answer I'll accept it. – icc97 Mar 03 '12 at 18:10

3 Answers3

19

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.

dash
  • 89,546
  • 4
  • 51
  • 71
1

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'

Sergey Benner
  • 4,421
  • 2
  • 22
  • 29
  • `mysql.proc` table was removed in MySQL 8.0. see https://ocelot.ca/blog/blog/2017/08/22/no-more-mysql-proc-in-mysql-8-0/ – Feiga Lubow Feb 02 '23 at 13:05
0

I wrote a procedure that returns a list of parameters in the table. It may be useful to someone.

CREATE PROCEDURE Micropsitta.'getParams'(parname varchar(255)) 
    BEGIN
    set @wyraz=(select cast(param_list as char)
    from mysql.proc 
    where type='PROCEDURE'  
    and name COLLATE utf8_polish_ci = parname COLLATE utf8_polish_ci);
    drop TEMPORARY table if exists tab;
    CREATE TEMPORARY TABLE tab (substrings varchar(255)) ENGINE=MEMORY;
    IF ((@wyraz) <> '')
     THEN
     set @firstchar=1;
     set @spacje=1;
     set @lenghtWyraz=(select CHAR_LENGTH(@wyraz));
     set @lenght=1;
     while @lenght < @lenghtWyraz
     DO
      set @lenght=(select CHAR_LENGTH(SUBSTRING_INDEX(@wyraz,' ',@spacje)));
      set @sub=(select SUBSTRING(@wyraz,@firstchar,@lenght-@firstchar+1));
      set @firstchar=(select CHAR_LENGTH(SUBSTRING_INDEX(@wyraz,',',@spacje)))+2;
      insert into tab (substrings)
      select @sub;
      set @spacje=@spacje+1;
     end while;
    END IF;
    select substrings as params from tab where substrings <>'';
    END;
ravkry
  • 1