Back in July 2012 I wrote this post
Query to find and replace text in all tables and fields of a mysql dbQuery 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:
SQL="SELECT CONCAT('SELECT ',QUOTE(db),',',QUOTE(tb),',',"
SQL="${SQL} QUOTE(col),',COUNT(1) FieldHasIt FROM ',db,'.',tb,'"
SQL="${SQL} WHERE \`',col,'\`=''',SearchString,''';') SearchSQL FROM"
SQL="${SQL} (SELECT table_schema db,table_name tb,column_name col FROM"
SQL="${SQL} information_schema.columns WHERE table_schema='store_qa'"
SQL="${SQL} AND (column_type LIKE 'char(%' OR column_type LIKE 'varchar(%'"
SQL="${SQL} OR column_type LIKE '%text')) A,(SELECT 'Hello' SearchString) B;"
mysql -uroot -p... -ANe"${SQL}" > MegaSearch.sql
mysql -uroot -p... -AN < MegaSearch.sql > MegaSearchResults.txt
RESULTS_FOUND=`grep -c "1$" < MegaSearchResults.txt`
echo ${RESULTS_FOUND}
if [ ${RESULTS_FOUND} -gt 0 ] ; then grep "1$" < MegaSearchResults.txt ; fi
The output tells you the Database, Table, and Column the data appears in.
Give it a Try !!!.
EDIT:
CONCAT('SELECT ',QUOTE(db),',',QUOTE(tb),',',QUOTE(col),',COUNT(1) FieldHasIt
FROM ',db,'.',tb,' WHERE \`',col,'\`=''',SearchString,''';')
SHOULD: add the two back ticks shown below for tables which have a space in the name.
db,'.`',tb,'`',' WHERE
