Clash Royale CLAN TAG#URR8PPP

Search in all fields from every table of a MySQL database

I want to search in all fields from all tables of a MySQL database a given string, possibly using syntax as:

SELECT * FROM * WHERE * LIKE '%stuff%'


Is it possible to do something like this?

23 Answers
23

You can peek into the information_schema schema. It has a list of all tables and all fields that are in a table. You can then run queries using the information that you have gotten from this table.

information_schema

The tables involved are SCHEMATA, TABLES and COLUMNS. There are foreign keys such that you can build up exactly how the tables are created in a schema.

information_schema

information_schema

You could do an SQLDump of the database (and its data) then search that file.

SQLDump

mysqldump -T

grep <search> *

If you have phpMyAdmin installed use its 'Search' feature.

I have used this on up to 250 table/10GB databases (on a fast server) and the response time is nothing short of amazing.

You can use this project:
http://code.google.com/p/anywhereindb

This will search all the data in all table.

function searchAllDB($search)
global $mysqli;

$out = "";

$sql = "show tables";
$rs = $mysqli->query($sql);
if($rs->num_rows > 0)
while($r = $rs->fetch_array())
$table = $r[0];
$out .= $table.";";
$sql_search = "select * from ".$table." where ";
$sql_search_fields = Array();
$sql2 = "SHOW COLUMNS FROM ".$table;
$rs2 = $mysqli->query($sql2);
if($rs2->num_rows > 0)
while($r2 = $rs2->fetch_array())
$colum = $r2[0];
$sql_search_fields = $colum." like('%".$search."%')";

$rs2->close();

$sql_search .= implode(" OR ", $sql_search_fields);
$rs3 = $mysqli->query($sql_search);
$out .= $rs3->num_rows."n";
if($rs3->num_rows > 0)
$rs3->close();


$rs->close();


return $out;



If you are avoiding stored procedures like the plague, or are unable to do a mysql_dump due to permissions, or running into other various reasons.

stored procedures

mysql_dump

I would suggest a three-step approach like this:

1) Where this query builds a bunch of queries as a result set.

# =================
# VAR/CHAR SEARCH
# =================
# BE ADVISED USE ANY OF THESE WITH CAUTION
# DON'T RUN ON YOUR PRODUCTION SERVER
# ** USE AN ALTERNATE BACKUP **

SELECT
CONCAT('SELECT * FROM ', A.TABLE_SCHEMA, '.', A.TABLE_NAME,
' WHERE ', A.COLUMN_NAME, ' LIKE '%stuff%';')
FROM INFORMATION_SCHEMA.COLUMNS A
WHERE
A.TABLE_SCHEMA != 'mysql'
AND A.TABLE_SCHEMA != 'innodb'
AND A.TABLE_SCHEMA != 'performance_schema'
AND A.TABLE_SCHEMA != 'information_schema'
AND
(
A.DATA_TYPE LIKE '%text%'
OR
A.DATA_TYPE LIKE '%char%'
)
;


.

# =================
# NUMBER SEARCH
# =================
# BE ADVISED USE WITH CAUTION

SELECT
CONCAT('SELECT * FROM ', A.TABLE_SCHEMA, '.', A.TABLE_NAME,
' WHERE ', A.COLUMN_NAME, ' IN ('%1234567890%');')
FROM INFORMATION_SCHEMA.COLUMNS A
WHERE
A.TABLE_SCHEMA != 'mysql'
AND A.TABLE_SCHEMA != 'innodb'
AND A.TABLE_SCHEMA != 'performance_schema'
AND A.TABLE_SCHEMA != 'information_schema'
AND A.DATA_TYPE IN ('bigint','int','smallint','tinyint','decimal','double')
;


.

# =================
# BLOB SEARCH
# =================
# BE ADVISED THIS IS CAN END HORRIFICALLY IF YOU DONT KNOW WHAT YOU ARE DOING
# YOU SHOULD KNOW IF YOU HAVE FULL TEXT INDEX ON OR NOT
# MISUSE AND YOU COULD CRASH A LARGE SERVER
SELECT
CONCAT('SELECT CONVERT(',A.COLUMN_NAME, ' USING utf8) FROM ', A.TABLE_SCHEMA, '.', A.TABLE_NAME,
' WHERE CONVERT(',A.COLUMN_NAME, ' USING utf8) IN ('%someText%');')
FROM INFORMATION_SCHEMA.COLUMNS A
WHERE
A.TABLE_SCHEMA != 'mysql'
AND A.TABLE_SCHEMA != 'innodb'
AND A.TABLE_SCHEMA != 'performance_schema'
AND A.TABLE_SCHEMA != 'information_schema'
AND A.DATA_TYPE LIKE '%blob%'
;


Results should look like this:

2) You can then just Right Click and use the Copy Row (tab separated)

Right Click

Copy Row (tab separated)

3) Paste results in a new query window and run to your hearts content.

Detail: I exclude system schema's that you may not usually see in your workbench unless you have the option Show Metadata and Internal Schemas checked.

Show Metadata and Internal Schemas

I did this to provide a quick way to ANALYZE an entire HOST or DB if needed, or to run OPTIMIZE statements to support performance improvements.

ANALYZE

OPTIMIZE

I'm sure there are different ways you may go about doing this but here’s what works for me:

-- ========================================== DYNAMICALLY FIND TABLES AND CREATE A LIST OF QUERIES IN THE RESULTS TO ANALYZE THEM
SELECT CONCAT('ANALYZE TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbname';

-- ========================================== DYNAMICALLY FIND TABLES AND CREATE A LIST OF QUERIES IN THE RESULTS TO OPTIMIZE THEM
SELECT CONCAT('OPTIMIZE TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbname';


Tested On MySQL Version: 5.6.23

WARNING: DO NOT RUN THIS IF:

You are unsure what you are doing.

You are trying to anger you DBA. (you may have people at your desk with the quickness.)

Cheers, Jay ;-]

I also did my own mysql crawler to search some wordpress configuration, was unable to find it in both the interface and database, and database dumps were too heavy and unreadable. I must say I can't do without it now.

It works like the one from @Olivier, but it manages exotic database / table names and is LIKE-joker safe.

<?php

$database = 'database';
$criteria = '*iemblo'; // you can use * and ? as jokers

$dbh = new PDO("mysql:host=127.0.0.1;dbname=$database;charset=utf8", 'root', '');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$tables = $dbh->query("SHOW TABLES");
while (($table = $tables->fetch(PDO::FETCH_NUM)) !== false)

$fields = $dbh->prepare("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?");
$fields->execute(array ($database, $table[0]));

$ors = array ();
while (($field = $fields->fetch(PDO::FETCH_NUM)) !== false)

$ors = str_replace("`", "``", $field[0]) . " LIKE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(:search, '\\', '\\\\'), '%', '\%'), '_', '\_'), '*', '%'), '?', '_')";


$request = 'SELECT * FROM ';
$request .= str_replace("`", "``", $table[0]);
$request .= ' WHERE ';
$request .= implode(' OR ', $ors);
$rows = $dbh->prepare($request);

$rows->execute(array ('search' => $criteria));

$count = $rows->rowCount();
if ($count == 0)

continue;


$str = "Table '$table[0]' contains $count rows matching '$criteria'.";
echo str_repeat('-', strlen($str)), PHP_EOL;
echo $str, PHP_EOL;
echo str_repeat('-', strlen($str)), PHP_EOL;

$counter = 1;
while (($row = $rows->fetch(PDO::FETCH_ASSOC)) !== false)

$col = 0;
$title = "Row #$counter:";
echo $title;
foreach ($row as $column => $value)

echo
(($col++ > 0) ? str_repeat(' ', strlen($title) + 1) : ' '),
$column, ': ',
trim(preg_replace('!s+!', ' ', str_replace(array ("r", "t", "n"), array ("", "", " "), $value))),
PHP_EOL;

echo PHP_EOL;
$counter++;




Running this script could output something like:

---------------------------------------------------
Table 'customers' contains 1 rows matching '*iemblo'.
---------------------------------------------------
Row #1: email_client: my@email.com
numero_client_compta: C05135
nom_client: Tiemblo
adresse_facturation_1: 151, My Street
adresse_facturation_2:
ville_facturation: Nantes
code_postal_facturation: 44300
pays_facturation: FR
numero_tva_client:
zone_geographique: UE
prenom_client: Alain
commentaires:
nom_societe:
email_facturation: my@email.com


This is the simplest query to retrive all Columns and Tables

SELECT * FROM information_schema.`COLUMNS` C WHERE TABLE_SCHEMA = 'YOUR_DATABASE'


All the tables or those with specific string in name could be searched via Search tab in phpMyAdmin.

Have Nice Query... ^.^/

Using MySQL Workbench it's easy to select several tables and run a search for text in all those tables of the DB ;-)

I am use HeidiSQL is a useful and reliable tool designed for web developers using the popular MySQL server.

In HeidiSQL you can push shift + ctrl + f and you can find text on the server in all tables. This option is very usefully.

Here is my solution for this

DROP PROCEDURE IF EXISTS findAll;
CREATE PROCEDURE `findAll`( IN `tableName` VARCHAR( 28 ) , IN `search` TEXT )
BEGIN
DECLARE finished INT DEFAULT FALSE ;
DECLARE columnName VARCHAR ( 28 ) ;
DECLARE stmtFields TEXT ;
DECLARE columnNames CURSOR FOR
SELECT DISTINCT `COLUMN_NAME` FROM `information_schema`.`COLUMNS`
WHERE `TABLE_NAME` = tableName ORDER BY `ORDINAL_POSITION` ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = TRUE;
SET stmtFields = '' ;
OPEN columnNames ;
readColumns: LOOP
FETCH columnNames INTO columnName ;
IF finished THEN
LEAVE readColumns ;
END IF;
SET stmtFields = CONCAT(
stmtFields , IF ( LENGTH( stmtFields ) > 0 , ' OR' , '' ) ,
' `', tableName ,'`.`' , columnName , '` REGEXP "' , search , '"'
) ;
END LOOP;
SET @stmtQuery := CONCAT ( 'SELECT * FROM `' , tableName , '` WHERE ' , stmtFields ) ;
PREPARE stmt FROM @stmtQuery ;
EXECUTE stmt ;
CLOSE columnNames ;
END;


CALL findAll('tbl_test','abb')

You could use

SHOW TABLES;


Then get the columns in those tables (in a loop) with

SHOW COLUMNS FROM table;


and then with that info create many many queries which you can also UNION if you need.

But this is extremely heavy on the database. Specially if you are doing a LIKE search.

SHOW TABLES FROM <db_name>

Dumping the SQL file was probably the quickest and fastest for me. Also discovered another problem anyways..

I modified the PHP answer of Olivier a bit to:

show total number of results

function searchAllDB($search)
global $mysqli;

$out = "";
$total = 0;
$sql = "SHOW TABLES";
$rs = $mysqli->query($sql);
if($rs->num_rows > 0)
while($r = $rs->fetch_array())
$table = $r[0];
$sql_search = "select * from ".$table." where ";
$sql_search_fields = Array();
$sql2 = "SHOW COLUMNS FROM ".$table;
$rs2 = $mysqli->query($sql2);
if($rs2->num_rows > 0)
while($r2 = $rs2->fetch_array())
$colum = $r2[0];
$sql_search_fields = $colum." like('%".$search."%')";
if(strpos($colum,$search))

echo "FIELD NAME: ".$colum."n";


$rs2->close();

$sql_search .= implode(" OR ", $sql_search_fields);
$rs3 = $mysqli->query($sql_search);
if($rs3 && $rs3->num_rows > 0)

$out .= $table.": ".$rs3->num_rows."n";
if($rs3->num_rows > 0)
$total += $rs3->num_rows;
$out.= print_r($rs3->fetch_all(),1);
$rs3->close();



$out .= "nnTotal results:".$total;
$rs->close();

return $out;



Although this question is old , here is how you can do it if you are using mysql workbench 6.3. ( Most likely it also works for other versions)

Right click your schema and "Search table data" , enter your value and hit "Start Search". Thats it.

This solution

a) is only MySQL, no other language needed, and

b) returns SQL results, ready for processing!

#Search multiple database tables and/or columns
#Version 0.1 - JK 2014-01
#USAGE: 1. set the search term @search, 2. set the scope by adapting the WHERE clause of the `information_schema`.`columns` query
#NOTE: This is a usage example and might be advanced by setting the scope through a variable, putting it all in a function, and so on...

#define the search term here (using rules for the LIKE command, e.g % as a wildcard)
SET @search = '%needle%';

#settings
SET SESSION group_concat_max_len := @@max_allowed_packet;

#ini variable
SET @sql = NULL;

#query for prepared statement
SELECT
GROUP_CONCAT("SELECT '",`TABLE_NAME`,"' AS `table`, '",`COLUMN_NAME`,"' AS `column`, `",`COLUMN_NAME`,"` AS `value` FROM `",TABLE_NAME,"` WHERE `",COLUMN_NAME,"` LIKE '",@search,"'" SEPARATOR "nUNIONn") AS col
INTO @sql
FROM `information_schema`.`columns`
WHERE TABLE_NAME IN
(
SELECT TABLE_NAME FROM `information_schema`.`columns`
WHERE
TABLE_SCHEMA IN ("my_database")
&& TABLE_NAME IN ("my_table1", "my_table2") || TABLE_NAME LIKE "my_prefix_%"
);

#prepare and execute the statement
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


#1243 - Unknown prepared statement handler (stmt) given to EXECUTE

Export the whole database and search in .sql file.

If you are using phpMyAdmin follow this answer, that site this reference.

phpMyAdmin

I used Union to string together queries. Don't know if it's the most efficient way, but it works.

SELECT * FROM table1 WHERE name LIKE '%Bob%' Union
SELCET * FROM table2 WHERE name LIKE '%Bob%';


There is a nice library for reading all tables, ridona

$database = new ridonaDatabase('mysql:dbname=database_name;host=127.0.0.1', 'db_user','db_pass');

foreach ($database->tables()->by_entire() as $row)

....do




I don't know if this is only in the recent versions, but right clicking on the Tables option in the Navigator pane pops up an option called Search Table Data. This opens up a search box where you fill in the search string and hit search.

Tables

Navigator

Search Table Data

You do need to select the table you want to search in on the left pane. But if you hold down shift and select like 10 tables at a time, MySql can handle that and return results in seconds.

For anyone that is looking for better options! :)

I built on a previous answer and have this, some extra padding just to be able to conveniently join all the output:

SELECT
CONCAT('SELECT ''',A.TABLE_NAME, '-' ,A.COLUMN_NAME,''' FROM ', A.TABLE_SCHEMA, '.', A.TABLE_NAME,
' WHERE ', A.COLUMN_NAME, ' LIKE '%Value%' UNION')
FROM INFORMATION_SCHEMA.COLUMNS A
WHERE
A.TABLE_SCHEMA != 'mysql'
AND A.TABLE_SCHEMA != 'innodb'
AND A.TABLE_SCHEMA != 'performance_schema'
AND A.TABLE_SCHEMA != 'information_schema'
UNION SELECT 'SELECT '''

-- for exact match use: A.COLUMN_NAME, ' LIKE 'Value' instead


First you run this, then paste in and run the result (no editing) and it will display all the table names and columns where the value is used.

WHERE NOT A.TABLE_SCHEMA IN ('mysql', 'innodb', 'performance_schema', 'information_schema')

i got this to work. you just need to change the variables

$query ="SELECT `column_name` FROM `information_schema`.`columns` WHERE `table_schema`='" . $_SESSION['db'] . "' AND `table_name`='" . $table . "' ";
$stmt = $dbh->prepare($query);
$stmt->execute();
$columns = $stmt->fetchAll(PDO::FETCH_ASSOC);

$query="SELECT name FROM `" . $database . "`.`" . $table . "` WHERE ( ";
foreach ( $columns as $column )
$query .=" CONVERT( `" . $column['column_name'] . "` USING utf8 ) LIKE '%" . $search . "%' OR ";

$query = substr($query, 0, -3);
$query .= ")";

echo $query . "<br>";
$stmt=$dbh->prepare($query);
$stmt->execute();
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo "<pre>";
print_r ($results );
echo "</pre>";


Thank you for your interest in this question.
Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).



Would you like to answer one of these unanswered questions instead?