Created
May 2, 2012 15:08
-
-
Save dwurf/2577305 to your computer and use it in GitHub Desktop.
MySQL hacks:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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