Search text in fields in every table of a MySQL database

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

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.

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.

This is the simple way that's i know. Select your DB in PHPMyAdmin, and go to the "Search" tab and write what you want to find and where you will searching for. Select all tables if you will search the words from all tables. Then "GO" and look the result.

It's been twelve years and no one posted an answer to the following question:

I want to search in all fields from all tables of a MySQL database for a given string

Anwsers include GUIs, vague ideas, syntax errors, procedures needing table names or prefixes and all sorts of contortions. Here's an actual, working, tested, simple to use answer building on multiple previous answers but also adding the primary key to the results.

DROP PROCEDURE IF EXISTS findAll;
DELIMITER $$
CREATE PROCEDURE findAll( IN `search` TEXT )
BEGIN
    SET SESSION group_concat_max_len := @@max_allowed_packet;
    SELECT GROUP_CONCAT(
        "SELECT '", c1.TABLE_NAME, "' AS `table`, '", c1.COLUMN_NAME, "' AS `column`, ",
        "CONCAT_WS(',', ",  (SELECT GROUP_CONCAT(c2.column_name) FROM `information_schema`.`columns` c2 WHERE c1.TABLE_SCHEMA=c2.TABLE_SCHEMA AND c1.TABLE_NAME=c2.TABLE_NAME AND c2.COLUMN_KEY='PRI') ,") AS pri,", 
        c1.COLUMN_NAME, " AS value FROM ", c1.TABLE_NAME,
      " WHERE `",c1.COLUMN_NAME,"` LIKE '%", search, "%'" SEPARATOR "\nUNION\n") AS col 
    INTO @sql   
    FROM information_schema.columns c1 
    WHERE c1.TABLE_SCHEMA = DATABASE();
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END $$
DELIMITER ;

That's it. You can now do CALL findAll('foobar');

Except not. You will run into two problems:

1. MySQL error 1436: Thread stack overrun
2. Prepared statement needs to be re-prepared.

Add the following two lines to /etc/mysql/mysql.conf.d/mysqld.cnf or wherever your cnf is or save them in a separate file and copy into the conf.d directory.

thread_stack            = 2M
table_definition_cache  = 5000

And yes, obviously this shouldn't be run on production because it's insecure and it'll tank your performance.

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... \^.^/

MySQL Workbench

Here are some instructions.

Download and install MSQL Workbench.

https://www.mysql.com/products/workbench/

When installing, it might require you to install Visual Studio C++ Redistributable. You can get it here:

https://support.microsoft.com/en-us/help/2977003/the-latest-supported-visual-c-downloads

x64: vc_redist.x64.exe (for 64 bit Windows)

When you open MySQL Workbench, you will have to enter your host name, user and password.

There is a Schemas tab on the side menu bar, click on the Schemas tab, then double click on a database to select the database you want to search.

Then go to menu Database - Search Data, and enter the text you are searching for, click on Start Search.

HeidiSql

Download and install HeidiSql https://www.heidisql.com/download.php

Enter your hostname, user and password.

Hit Ctrl+Shift+F to search text.

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.

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.

Even if the following proposal should not be considered as a final solution you can achieve the goal by doing something like this:

SET SESSION group_concat_max_len = 1000000;
SET @search = 'Text_To_Search';

DROP table IF EXISTS table1;
CREATE TEMPORARY TABLE table1 AS 
(SELECT 
    CONCAT('SELECT \'',TABLE_NAME,'\' as \'table_name\',\'',COLUMN_NAME,'\' as \'column_name\',CONVERT(count(*),char) as \'matches\' FROM `',
    TABLE_NAME,'` where `',COLUMN_NAME,'` like \'%',@search,'%\' UNION ') as 'query'
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'db_name' limit 1000000);

set @query = (SELECT GROUP_CONCAT(t1.`query` SEPARATOR '') as 'final_query' from table1 t1 limit 1);
set @query = (SELECT SUBSTRING(@query, 1, length(@query) - 7));

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Please remember that:

1. Options: group_concat_max_len and limit 1000000 not always are needed, it will depends of your server/IDE configuration. Just in case I added them.

2. After executing this you will get a 3 column response: [table_name], [column_name], [matches]

3. Column 'matches' is the number of occurrences in the given table/column.

4. This query is very fast.

DISCLAIMER: It would be useful only for personal use, in other words please don't use it in a production system, because it is sensitive to SQL Injection attacks given that the search parameter is concatenated with other strings. If you want to create a prod. ready function, then you will need to create a store procedure with a LOOP.

To search a string in all tables in a database run the following command on CLI.

mysqldump -u UserName --no-create-info --extended-insert=FALSE DBName -p | grep -i "searchingString"

Or,

mysqldump -u UserName --no-create-info --extended-insert=FALSE DBName -p | grep -i "searchingString" > searchingString.sql

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>";

I have done this using HeidiSQL. It's not easy to find but by pressing Ctrl+Shift+F it displays the "table tools" dialogue. Then select what you want to search (Full database to single table) and enter the "Text to find" value and click "Find". I found it surprisingly fast (870MiB db in less than a minute)

In case 23 answers is not enough, here are 2 more... Depending on database structure and content, you may find one them to actually be a quick and simple solution.

For fans of shell one-liners, here is a long one (actually on 2 lines to use variables):

cmd='mysql -u Username -pYour_Password -D Your_Database' # <-- Adapt this

$cmd -s -e 'SHOW TABLES' | while read table; do echo "=== $table ==="; $cmd -B -s -e "SELECT * FROM $table" | grep 'Your_Search'; done

Or on multiple lines to make it more readable:

$cmd -s -e 'SHOW TABLES' \
| while read table; do
    echo "=== $table ===";
    $cmd -B -s -e "SELECT * FROM $table" \
    | grep 'Your_Search';
  done

• -s (--silent) is to suppress the column name headers

• -B (--batch) escapes special characters like newlines, so we get the whole record when using grep

And for Perl fans, this will let you use regular expressions:

# perl -MDBI -le '($db,$u,$p)=@ARGV; $dbh=DBI->connect("dbi:mysql:dbname=$db",$u,$p); foreach $table ($dbh->tables()) {print "$table\n"; foreach $r ($dbh->selectall_array("SELECT * FROM $table")) {$_=join("\t", @$r); print $_ if (/Your_Regex/);}}' Your_Database Username Your_Password

Which in a "real" Perl script could be something like this:

#!/usr/bin/perl

use strict;
use open qw(:std :utf8);

use DBI;

my $db_host  = 'localhost';
my $db       = 'Your_Database';
my $db_user  = 'Username';
my $db_pass  = 'Your_Password';

my $search    = qr/Your_regex_Search/;


# https://metacpan.org/pod/DBD::mysql
my $dbh = DBI->connect( "dbi:mysql:dbname=$db;host=$db_host", $db_user, $db_pass,
                        { mysql_enable_utf8mb4 => 1 }
) or die "Can't connect: $DBI::errstr\n";


foreach my $table ( $dbh->tables() ) {
    my $sth = $dbh->prepare("SELECT * FROM $table")
        or die "Can't prepare: ", $dbh->errstr;

    $sth->execute
        or die "Can't execute: ", $sth->errstr;

    my @results;

    while (my @row = $sth->fetchrow()) {
        local $_ = join("\t", @row);
        if ( /$search/ ) {
            push @results, $_;
        }
    }

    $sth->finish;

    next unless @results;

    print "*** TABLE $table :\n",
          join("\n---------------\n", @results),
          "\n" . "=" x 20 . "\n";
}

$dbh->disconnect;

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()->by_entire() as $row) {

....do

}

If you are not using it on code level, you just want to check the information, you could export the entire database as SQL and then search on the text editor.