We don't always recall exactly what database(s) certain tables, columns and routine names are in, so ...
-- ---------------------------------------------
-- 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 |
 |