way to search entire database for a string in MySQL

Question

I am working on a MySQL database that is huge (about 120 tables). I am trying to make some sense of it and it will help a great deal if I can search all 120 tables + columns for a string I am looking for.

Is that possible to do on a MySQL DB?

Answer 1

This will help you to find a string in entire database

DELIMITER ##
 CREATE PROCEDURE sp_search1(IN searchstring INT)
BEGIN
 DECLARE done INT DEFAULT FALSE;
 DECLARE output TEXT;
  DECLARE table_name TEXT;
  DECLARE column_name TEXT;
DECLARE s TEXT;  
 DECLARE searchcursor CURSOR FOR 
SELECT table_name,column_name  FROM information_schema.columns AS column    
ORDER BY table_name,ordinal_position;   
OPEN searchcursor;
PREPARE stmt2 FROM 'select * from ? where ? = ?' ;  
search_loop : LOOP
IF done THEN 
LEAVE search_loop;
END IF;              
FETCH searchcursor INTO table_name,column_name;
IF(     EXECUTE stmt2 USING table_name, column_name,searchstring) THEN
 INSERT INTO `table_names`(`table_name`) VALUES(@table_name);    
    END IF;
END LOOP;   
END;