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.
$ mysqldump -pPASSWORD database --extended=FALSE | grep pattern | less -S
More: http://winashwin.wordpress.com/2012/08/28/mysql-search/
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 !!!.
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
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.
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 customer.name" 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.
You can return all columns from all tables that match any values you're looking for all at once by building off of RolandoMySQLDBA's answer and using PREPARE and EXECUTE to run the query.
-- Your values
SET @table_schema = 'your_table_name';
SET @condition = "LIKE '%your string to search%'";
SET @column_types_regexp = '^((var)?char|(var)?binary|blob|text|enum|set)\\(';
-- Reset @sql_query in case it was used previously
SET @sql_query = '';
-- Build query for each table and merge with previous queries with UNION
SELECT
-- Use `DISTINCT IF(QUERYBUILDING, NULL, NULL)`
-- to only select a single null value
-- instead of selecting the query over and over again as it's built
DISTINCT IF(@sql_query := CONCAT(
IF(LENGTH(@sql_query), CONCAT(@sql_query, " UNION "), ""),
'SELECT ',
QUOTE(CONCAT('`', `table_name`, '`.`', `column_name`, '`')), ' AS `column`, ',
'COUNT(*) AS `occurrences` ',
'FROM `', `table_schema`, '`.`', `table_name`, '` ',
'WHERE `', `column_name`, '` ', @condition
), NULL, NULL) `query`
FROM (
SELECT
`table_schema`,
`table_name`,
`column_name`
FROM `information_schema`.`columns`
WHERE `table_schema` = @table_schema
AND `column_type` REGEXP @column_types_regexp
) `results`;
select @sql_query;
-- Only return results with at least one occurrence
SET @sql_query = CONCAT("SELECT * FROM (", @sql_query, ") `results` WHERE `occurrences` > 0");
-- Run built query
PREPARE statement FROM @sql_query;
EXECUTE statement;
DEALLOCATE PREPARE statement;
If you have phpMyAdmin installed use its 'Search' feature.
Select your DataBase Be sure you do have selected DataBase , not a table, otherwise you'll get a completely different search dialog
If you can use a bash - here is a script: It needs a user dbread with pass dbread on the database.
#!/bin/bash
IFS='
'
DBUSER=dbread
DBPASS=dbread
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: "
read SEARCHSTRING
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\``
do
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}'`
do
if [ `mysql $DB -u$DBUSER -p$DBPASS -e "Select * from $i where $k='$SEARCHSTRING'" | wc -l` -gt 1 ]
then
echo " Your searchstring was found in table $i, column $k"
fi
done
done
If anyone wants an explanation: http://infofreund.de/?p=1670
If you are using MySQL Workbench, right click on the database schema and select "Search Table Data..." then fill out the form. This will search the entire database. When (or if) the results come up, click on the arrow to expand. It will show the schema, table, primary key data, column name and the actual data that matches your search.
Tip: If nothing comes up, try widen your search.
Please note that this is only really useful for searching text (char, varchar and text) data types.
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 );
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')
There is a nice library for reading all tables, ridona
$database = new ridona\Database('mysql:dbname=database_name;host=127.0.0.1', 'db_user','db_pass');
foreach ($database->tables(['table_name1','table_name2'])->by_entire() as $row) {
do...
}
Use This Trick with One Line of Code
SELECT * FROM `table_name1`,`table_name2`
WHERE '$data' IN (`column_name1`,`column_name2`,`column_name3`,`column_name4`);
column_name : Type all the columns name in which you search.
table_name : Type all the Table name in which you search.
