Isso não é um tarefa normal muito menos rotineira. Porém volta e meia me deparo com situações onde eu ou colegas necessitam localizar algum valor no MySQL, porém sem fazer ideia de em qual tabela se encontra.
Para isso desenvolvi essa procedure.
Ela se encarrega de procurar algum valor em todas as colunas de todas.

Use com moderação, pois dependendo da quantidade de dados que você tiver, pode afetar o desempenho do servidor.

DELIMITER $
CREATE PROCEDURE sp_searchValueInDB(_P_VALUE_ varchar(100), _P_DB_ varchar(64), _P_TYPE_ ENUM('char', 'numeric', 'date'))
BEGIN
 -- This procedure performs searches in all columns of a database.
 -- The execution performance will depend greatly on the size of your database.
 -- Contacts: Diego Hellas/diegohellas@gmail.com
 -- 2014-10-13
 -- _P_VALUE_: what is sought
 -- _P_DB_: Database name for search
 -- _P_TYPE_: Type of data that will be fetched, it helps reduce the columns to be searched, which makes it faster.
 DECLARE v_collumn VARCHAR(64);
 DECLARE v_table VARCHAR(64);
 DECLARE v_query VARCHAR(500);
 DECLARE v_finished INTEGER DEFAULT 0;
 DECLARE cursor_collumns CURSOR FOR SELECT COLUMN_NAME, TABLE_NAME FROM tmp_collumns;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;

 -- Drop tmp table if exists
 SET @v_query = CONCAT('DROP TABLE IF EXISTS tmp_collumns'); 
 PREPARE dropTable from @v_query; 
 EXECUTE dropTable; 
 DEALLOCATE PREPARE dropTable; 

 -- Cretae tmp table for the CURSOR use
 SET @v_query = CONCAT('CREATE TABLE tmp_collumns (COLUMN_NAME varchar(64), TABLE_NAME varchar(64))'); 
 PREPARE createTable from @v_query; 
 EXECUTE createTable; 
 DEALLOCATE PREPARE createTable; 
 
 -- Select only the types of expected columns
 IF _P_TYPE_ = 'char' THEN
 INSERT INTO tmp_collumns SELECT COLUMN_NAME, TABLE_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = _P_DB_ AND DATA_TYPE IN("CHAR", "VARCHAR", "BINARY", "VARBINARY", "BLOB", "TEXT", "TINYTEXT" , "ENUM", "SET");
 END IF;
 IF _P_TYPE_ = 'numeric' THEN
 INSERT INTO tmp_collumns SELECT COLUMN_NAME, TABLE_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = _P_DB_ AND DATA_TYPE IN("tinyint", "smallint" "mediunint", "int", "bigint", "decimal", "bit", "double", "float");
 END IF;
 IF _P_TYPE_ = 'date' THEN
 INSERT INTO tmp_collumns SELECT COLUMN_NAME, TABLE_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = _P_DB_ AND DATA_TYPE IN("DATE","TIME", "DATETIME", "TIMESTAMP", "YEAR");
 END IF;

 
 -- CURSOR LOOP
 OPEN cursor_collumns;
 cursor_collumns: LOOP
 FETCH cursor_collumns INTO v_collumn, v_table;
 SET @qtyResults = 0;
 IF v_finished = 1 THEN 
 LEAVE cursor_collumns;
 END IF;
 set @v_query=concat('SELECT COUNT(*) FROM ', v_table, ' WHERE ', v_collumn, ' LIKE "%', _P_VALUE_, '%" INTO @qtyResults'); 
 prepare searchValueQuery from @v_query;
 execute searchValueQuery;
 IF @qtyResults > 1 THEN 
 SELECT v_table, v_collumn, @qtyResults;
 END IF;
 DEALLOCATE PREPARE searchValueQuery;
 END LOOP cursor_collumns;
 CLOSE cursor_collumns;


 -- Drop tmp table if exists
 SET @v_query = CONCAT('DROP TABLE IF EXISTS tmp_collumns'); 
 PREPARE dropTable from @v_query; 
 EXECUTE dropTable; 
 DEALLOCATE PREPARE dropTable; 
END$
DELIMITER ;

Após criar a procedure, basta fazer bom uso.
Ex: call sp_searchValueInDB(‘meu_db’, ‘admin’, ‘char’);

Aulas ao vivo sem que você precise sair de casa.

Post Navigation