How to search whole MySQL database for a particular string

Question

is it possible to search a whole database tables ( row and column) to find out a particular string.

I am having a Database named A with about 35 tables,i need to search for the string named "hello" and i dont know on which table this string is saved.Is it possible?

Using MySQL

i am a linux admin and i am not familiar with databases,it would be really helpful if u can explain the query also.

Answer 1

Do you have access to phpMyAdmin? It has a search feature for each database.

or use scripting of your choice on the result of (change dbname):

$string = 'mysqldump --compact --skip-extended-insert -u ' . $db_user . ' -p' . $db_password . ' dbname 2>&1 | grep "particular string" 2>&1';
$result = shell_exec ( $string );

Answer 2

I focus on a simple template solution for your question implemented in plsql for Oracle database, I hope customizing it on MySql, being simple throw googleing. all you need is to investigate information schema on MySql and replaceing some table name and columns.

In the script: ALL_TAB_COLUMNS is an information schema table containing All Table Columns 'VARCHAR2','NVARCHAR2','NCHAR','CHAR' refers to string column types in Oracle, you have to replace them with equvalents type names of MySql
%hello% is search keyword replace it with any string.

The result will be some or many sql scripts (based on your tables column types) and running them will result your answer
Also performance and time consumption will be another matters.
i hope this will be useful

SELECT 
'SELECT ' || ALL_TAB_COLUMNS.COLUMN_NAME  || ' AS RESULT_FOUND, ' 
|| '''' || ALL_TAB_COLUMNS.TABLE_NAME ||''''|| ' AS TABLE_NAME, '
|| '''' || ALL_TAB_COLUMNS.COLUMN_NAME ||''''|| ' AS COLUMN_NAME '
|| 'FROM ' || ALL_TAB_COLUMNS.OWNER ||'.'||ALL_TAB_COLUMNS.TABLE_NAME  
|| ' WHERE ' || ALL_TAB_COLUMNS.COLUMN_NAME || ' LIKE ''%hello%''' || ';'
FROM ALL_TAB_COLUMNS 
WHERE 
ALL_TAB_COLUMNS.OWNER = 'SCOTT'
AND 
ALL_TAB_COLUMNS.DATA_TYPE IN ('VARCHAR2','NVARCHAR2','NCHAR','CHAR')

Answer 3

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 ''',db,''',''',tb,''',''',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 NOT IN ('information_schema','mysql','performance_schema') 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 ''',db,''',''',tb,''',''',col,''',COUNT(1) FieldHasIt ',"
SQLSTMT="${SQLSTMT} 'FROM ',db,'.',tb, WHERE ',col,'=''',SearchString,''';') SearchSQL"
SQLSTMT="${SQLSTMT} FROM (SELECT table_schema db,table_name tb,column_name col"
SQLSTMT="${SQLSTMT} FROM information_schema.columns WHERE table_schema NOT IN"
SQLSTMT="${SQLSTMT} ('information_schema','mysql','performance_schema') AND"
SQLSTMT="${SQLSTMT} (column_type LIKE 'char(%'OR column_type LIKE 'varchar(%' OR "
SQLSTMT="${SQLSTMT} 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 !!!.