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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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: – a_horse_with_no_name Feb 17 '13 at 8:38
$ mysqldump -pPASSWORD database --extended=FALSE | grep pattern | less -S


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 ',QUOTE(db),',',QUOTE(tb),',',QUOTE(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 = '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="${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`
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 !!!.

share|improve this answer

If you can use a bash - here is a script: It needs a user dbread with pass dbread on the database.

echo -n "Which database do you want to search in (press 0 to see all databases): " 
read DB
echo -n "Which string do you want to search: " 
for i in `mysql $DB -u$DBUSER -p$DBPASS -e "show tables" | grep -v \`mysql $DB -u$DBUSER -p$DBPASS -e "show tables" | head -1\``
for k in `mysql $DB -u$DBUSER -p$DBPASS -e "desc $i" | grep -v \`mysql $DB -u$DBUSER -p$DBPASS -e "desc $i" | head -1\` | grep -v int | awk '{print $1}'`
if [ `mysql $DB -u$DBUSER -p$DBPASS -e "Select * from $i where $k='$SEARCHSTRING'" | wc -l` -gt 1 ]
echo " Your searchstring was found in table $i, column $k"

If anyone wants an explanation:

share|improve this answer

Taking another approach that doesn't require building SQL queries, I developed CRGREP as a free opensource command line tool that will grep databases (including MySQL) and supports both simple "fred" type searches for "fred" in the "name" column of the "customer" table to more complex pattern matching such as "fr?d *.author" for pattern matching against all "author" columns across all tables.

share|improve this answer

If you're Linux admin, you should be familiar with the command line, so this one would be handy:

$ mysqldump -u root -proot --skip-extended-insert db_name | grep --color=auto -w foo

Change root/root to your mysql credentials (or use ~/.my.cnf), db_name to your database name and foo for your searching text. Parameter --skip-extended-insert for mysqldump will display each query in separate lines. Parameter --color=auto for grep will highlight your string and -w will match the whole word.

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

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.