create procedure findroutine( pname varchar(64) )
SELECT 
  r.routine_schema as Db, 
  r.routine_name as Name, 
  p.parameters
FROM information_schema.routines r
JOIN (
  select 
    specific_schema,specific_name,routine_type, 
    group_concat(
      concat(parameter_name,' ',data_type) 
      order by ordinal_position separator ','
    ) as Parameters
  from information_schema.parameters 
  group by specific_schema,specific_name,routine_type 
) p ON r.routine_schema=p.specific_schema 
    AND r.routine_type=p.routine_type 
    AND r.routine_name=p.specific_name
WHERE r.routine_name like concat('%',pname,'%')
ORDER BY Db,r.routine_type,r.routine_name;

Last updated 8 Apr 2024