Skip to content

Instantly share code, notes, and snippets.

@dwurf
Created May 2, 2012 15:08
Show Gist options
  • Save dwurf/2577305 to your computer and use it in GitHub Desktop.
Save dwurf/2577305 to your computer and use it in GitHub Desktop.
MySQL hacks:
-- Search all varchar columns in an entire database for a specific value
-- DANGER: do not use on large databases. 10k rows **should** be fine, larger than that requires caution.
-- Run this script in a temp database. It needs access to the target database and information_schema to run.
-- Results will go into a temporary table called rset. Everything will be cleaned up once you disconnect.
-- BEGIN SCRIPT --
-- Variables. Modify these, the rest of the script will do the work for you.
set @dbname := 'mediatomb';
set @search := 'Kid Loco';
-- create results table
drop table if exists rset;
create temporary table rset(tbl varchar(255), col varchar(255), value varchar(255));
-- Create proc
drop procedure if exists dbscan;
delimiter $$
create procedure dbscan(IN schemaname VARCHAR(255), IN searchterm VARCHAR(255))
begin
declare done int default false;
declare tbl varchar(255);
declare col varchar(255);
declare cur1 cursor for select distinct table_name, column_name
from information_schema.columns
where table_schema = schemaname
and data_type in ('varchar', 'char', 'enum');
declare continue handler for not found set done = true;
open cur1;
read_loop: loop
fetch cur1 into tbl, col;
if done then
leave read_loop;
end if;
set @qry := concat('insert into rset select distinct "' , tbl,
'","', col, '",`', col ,'` from `' , schemaname , '`.`' ,
tbl , '` where binary `' , col , '` = "' , searchterm , '"');
prepare stmt1 from @qry;
execute stmt1;
deallocate prepare stmt1;
end loop;
close cur1;
end;
$$
delimiter ;
-- do the work
call dbscan(@dbname, @search);
-- cleanup
drop procedure dbscan;
-- show results
select * from rset;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment