Return to Revisions

Back in July 2012 I wrote this post

Query to find and replace text in all tables and fields of a mysql db

It uses the table information_schema.columns to pick up every CHAR, VARCHAR, and TEXT field and perform a textual REPLACE.

Please look over my old link and use its paradigm to do a search.

As an example, this will create a separate SELECT for each text column in every table

SELECT
    CONCAT('SELECT ',QUOTE(db),',',QUOTE(tb),',',QUOTE(col),'
    ,COUNT(1) FieldHasIt FROM ',db,'.',tb,' WHERE \`',col,'\`=''',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;

Create a Giant SQL text file with it. Then, execute that Giant SQL script:

SQLSTMT="SELECT CONCAT('SELECT ',QUOTE(db),',',QUOTE(tb),',',QUOTE(col),',COUNT(1) FieldHasIt FROM ',db,'.',tb,' WHERE \`',col,'\`=''',SearchString,''';') SearchSQL FROM (SELECT table_schema db,table_name tb,column_name col FROM information_schema.columns WHERE table_schema='store_qa' AND (column_type LIKE 'char(%' OR column_type LIKE 'varchar(%' OR column_type LIKE '%text')) A,(SELECT 'Hello' SearchString) B;"

mysql -uroot -p... -ANe"${SQLSTMT}" > MegaSearch.sql
mysql -uroot -p... -AN < MegaSearch.sql > MegaSearchResults.txt

Give it a Try !!!.