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
1  
See here: stackoverflow.com/a/5350405/330315 –  a_horse_with_no_name Feb 17 '13 at 8:38

5 Answers 5

$ 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

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')
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

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 !!!.

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, 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

Your Answer

 
discard

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.