Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
See here: stackoverflow.com/a/5350405/330315 –  a_horse_with_no_name Feb 17 '13 at 8:38

4 Answers 4

$ mysqldump -pPASSWORD database --extended=FALSE | grep pattern | less -S

More: http://winashwin.wordpress.com/2012/08/28/mysql-search/

share|improve this answer

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 );
share|improve this answer

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

|| ' WHERE ' || ALL_TAB_COLUMNS.COLUMN_NAME || ' LIKE ''%hello%''' || ';'
share|improve this answer

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

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

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.