Look up routine's params

from the Artful MySQL Tips List

Information_schema tracks stored routines and their properties. This sproc looks in information_schema for the first routine it can find for the specified db and with a routine name like the name provided:
drop procedure if exists routhlp;
delimiter go
create procedure routhlp( db varchar(64), rnam varchar(64) )
  select routine_name into rnam
  from information_schema.routines
  where routine_schema=db and routine_name like concat('%', rnam, '%' ) limit 1;
  if found_rows() > 0 then
      select routine_schema, routine_type, specific_name into @db, @typ, @nam
      from information_schema.routines
      where routine_schema=db and routine_name=rnam;
      set @params =
        ( select group_concat( 
            trim( concat( if(parameter_mode='IN','', parameter_mode), ' ', 
                          parameter_name, ' ', 
                          ifnull(character_maximum_length, '')
            order by ordinal_position
          from information_schema.parameters
          where specific_schema=@db and specific_name=@nam 
      select @db as db, @typ as type, @nam as routine, @params as params;
    select 'Routine not found' as result;
  end if;
delimiter ;

Last updated 19 Dec 2018

Return to the Artful MySQL Tips page