{ MySQL Stored Procedure }

MySQL procedure for searching for a keyword throughout an entire database.

Procedure:

DELIMITER ;;
CREATE PROCEDURE `get_tables`(in_search varchar(50))
    READS SQL DATA
BEGIN
 DECLARE trunc_cmd VARCHAR(50);
 DECLARE search_string VARCHAR(250);

 DECLARE db,tbl,clmn CHAR(50);
 DECLARE done INT DEFAULT 0;
 DECLARE COUNTER INT;

 DECLARE table_cur CURSOR FOR
 SELECT concat('SELECT COUNT(*) INTO @CNT_VALUE FROM `',table_schema,'`.`',table_name,'` WHERE `', column_name,'` REGEXP ''',in_search,''';')
 ,table_schema,table_name,column_name
 FROM information_schema.COLUMNS
 WHERE TABLE_SCHEMA NOT IN ('information_schema','test','mysql');

 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

 PREPARE trunc_cmd FROM "TRUNCATE TABLE temp_table;";
 EXECUTE trunc_cmd ;

 OPEN table_cur;
 table_loop:LOOP
 FETCH table_cur INTO search_string,db,tbl,clmn;
 
 SET @search_string = search_string;
 SELECT search_string;
 PREPARE search_string FROM @search_string;
 EXECUTE search_string;

 SET COUNTER = @CNT_VALUE;
 SELECT COUNTER;

 IF COUNTER>0 THEN
 
 INSERT INTO temp_table VALUES(db,tbl,clmn);
 END IF;

 IF done=1 THEN
 LEAVE table_loop;
 END IF;
 END LOOP;
 CLOSE table_cur;

 
 SELECT * FROM temp_table;
END;;
DELIMITER ;

Create temporary table:

CREATE TABLE `temp_table` (
  `t_schema` varchar(45) NOT NULL,
  `t_table` varchar(45) NOT NULL,
  `t_field` varchar(45) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Then call the procedure:

CALL get_tables('search_for_this')

The “temp_table” table will be populated with the tables and their associated columns that contain the specified string.