-- ---------------------------------------------
-- FIND COLUMN BY NAME, WILDCARDS ALLOWED
-- ---------------------------------------------
drop procedure if exists findcol;
delimiter go
create procedure findcol( pcolname varchar(64) )
begin
  if locate('%',pcolname) > 0 then
    select table_schema, table_name, column_name, data_type
    from information_schema.columns
    where column_name like pcolname;
  else
    select table_schema, table_name, column_name, data_type
    from information_schema.columns
    where column_name=pcolname;  
  end if;
end;
go
delimiter ;

-- ---------------------------------------------
-- FIND FUNCTION, STORED PROC OR TRIGGER BY NAME
-- ---------------------------------------------
drop procedure if exists findroutine;
create procedure findroutine( pname varchar(64) )
SELECT 
  r.routine_schema as Db, 
  r.routine_name as Name, 
  r.routine_type as 'Type',
  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,routine_name;

-- ---------------------------------------------
-- FIND TABLE BY NAME, WILDCARDS ALLOWED
-- ---------------------------------------------
delimiter ;
drop procedure if exists findtbl;
delimiter go
create procedure findtbl( pname varchar(64) )
begin
  if locate('%',pname) > 0 then
    select table_schema, table_name, engine, table_rows 
    from information_schema.tables
    where table_name like pname;
  else
    select table_schema, table_name, engine, table_rows 
    from information_schema.tables
    where table_name=pname;  
  end if;
end;
go
delimiter ;

Last updated 29 Apr 2020