mkubenka/search_mysql.md

## search_mysql.md

      
    Raw
  

              search_mysql.md
            
          
    How to search whole MySQL database for a particular string


https://dba.stackexchange.com/a/37041/7994

SELECT
    CONCAT('SELECT ',QUOTE(db),',',QUOTE(tb),',',QUOTE(col),',COUNT(1) FieldHasIt
    FROM `',db,'`.`',tb,'` WHERE \`',col,'\` LIKE ''%',SearchString,''%';') SearchSQL
FROM
(
    SELECT table_schema db,table_name tb,column_name col FROM information_schema.columns
    WHERE table_schema = 'mydb' AND
    (column_type LIKE 'char(%' OR column_type LIKE 'varchar(%' OR column_type LIKE '%text')
) A,(SELECT 'Hello' SearchString) B;
mysql -AN < prepare_search.sql > search.sql
mysql -AN < search.sql > results_search.txt

# Get table list with non-zero occurences.
grep -Ev "\s+0$" results_search.txt