Search text in fields in every table of a MySQL database

Question

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

User · Answer

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

User · Answer

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

User · Answer

I modified the PHP answer of Olivier a bit to    print out the results in which the string was found omit tables without results also show output if column names match the search input show total number of results  function searchAllDB  search       global  mysqli        out            total   0       sql    SHOW TABLES        rs    mysqli- gt query  sql       if  rs- gt num rows  gt  0           while  r    rs- gt fetch array                  table    r 0                sql search    select   from    table   where                 sql search fields   Array                 sql2    SHOW COLUMNS FROM    table               rs2    mysqli- gt query  sql2               if  rs2- gt num rows  gt  0                   while  r2    rs2- gt fetch array                          colum    r2 0                        sql search fields      colum   like      search                            if strpos  colum  search                                                 echo  FIELD NAME     colum   n                                                            rs2- gt close                               sql search    implode   OR     sql search fields                rs3    mysqli- gt query  sql search               if  rs3  amp  amp   rs3- gt num rows  gt  0                                 out     table       rs3- gt num rows   n                   if  rs3- gt num rows  gt  0                        total     rs3- gt num rows                       out   print r  rs3- gt fetch all   1                        rs3- gt close                                                       out      n nTotal results    total           rs- gt close              return  out

User · Answer

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    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      3  Paste results in a new query window and run to your heart s 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   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   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 concerned with causing Table-locks  keep an eye on your client-connections    You are unsure about what you are doing    You are trying to anger you DBA   you may have people at your desk with the quickness        Cheers  Jay  -

User · Answer

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

User · Answer

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     lt -- Adapt this   cmd -s -e  SHOW TABLES    while read table  do echo  quot      table     quot    cmd -B -s -e  quot SELECT   FROM  table quot    grep  Your Search   done  Or on multiple lines to make it more readable   cmd -s -e  SHOW TABLES      while read table  do     echo  quot      table     quot        cmd -B -s -e  quot SELECT   FROM  table quot          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- gt connect  quot dbi mysql dbname  db quot   u  p   foreach  table   dbh- gt tables     print  quot  table n quot   foreach  r   dbh- gt selectall array  quot SELECT   FROM  table quot        join  quot  t quot     r   print    if   Your Regex       Your Database Username Your Password  Which in a  quot real quot  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- gt connect   quot dbi mysql dbname  db host  db host quot    db user   db pass                            mysql enable utf8mb4   gt  1     or die  quot Can t connect   DBI  errstr n quot     foreach my  table    dbh- gt tables           my  sth    dbh- gt prepare  quot SELECT   FROM  table quot           or die  quot Can t prepare   quot    dbh- gt errstr        sth- gt execute         or die  quot Can t execute   quot    sth- gt errstr       my  results       while  my  row    sth- gt fetchrow              local      join  quot  t quot    row           if     search                  push  results                            sth- gt finish       next unless  results       print  quot     TABLE  table   n quot             join  quot  n--------------- n quot    results              quot  n quot     quot   quot  x 20    quot  n quot       dbh- gt disconnect

User · Answer

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   nUNION n   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            amp  amp  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

User · Answer

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

User · Answer

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

User · Answer

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- gt tables  - gt by entire   as  row         do

User · Answer

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

User · Answer

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

User · Answer

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- gt prepare  query    stmt- gt execute      columns    stmt- gt 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     lt br gt     stmt  dbh- gt prepare  query    stmt- gt execute     results    stmt- gt fetchAll PDO  FETCH ASSOC   echo   lt pre gt    print r   results    echo   lt  pre gt

User · Answer

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

User · Answer

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

User · Answer

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    lt  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- gt setAttribute PDO  ATTR ERRMODE  PDO  ERRMODE EXCEPTION     tables    dbh- gt query  SHOW TABLES    while    table    tables- gt fetch PDO  FETCH NUM       false         fields    dbh- gt prepare  SELECT COLUMN NAME FROM INFORMATION SCHEMA COLUMNS WHERE TABLE SCHEMA     AND TABLE NAME             fields- gt execute array   database   table 0           ors   array         while    field    fields- gt 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- gt prepare  request         rows- gt execute array   search    gt   criteria          count    rows- gt 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- gt fetch PDO  FETCH ASSOC       false                 col   0           title    Row    counter             echo  title          foreach   row as  column   gt   value                        echo                col    gt  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

User · Answer

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

User · Answer

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

User · Answer

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   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   After executing this you will get a 3 column response   table name    column name    matches   Column  matches  is the number of occurrences in the given table column   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

User · Answer

PHP function   function searchAllDB  search       global  mysqli        out             sql    show tables        rs    mysqli- gt query  sql       if  rs- gt num rows  gt  0           while  r    rs- gt fetch array                  table    r 0                out     table                   sql search    select   from    table   where                 sql search fields   Array                 sql2    SHOW COLUMNS FROM    table               rs2    mysqli- gt query  sql2               if  rs2- gt num rows  gt  0                   while  r2    rs2- gt fetch array                          column    r2 0                        sql search fields      column   like      search                                           rs2- gt close                               sql search    implode   OR     sql search fields                rs3    mysqli- gt query  sql search                out     rs3- gt num rows   n               if  rs3- gt num rows  gt  0                    rs3- gt close                                     rs- gt close               return  out

User · Answer

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

User · Answer

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

User · Answer

If you have phpMyAdmin installed use its  Search  feature    Select your DB Be sure you do have a DB selected  i e  not a table  otherwise you ll get a completely different search dialog  Click  Search  tab Choose the search term you want Choose the tables to search   I have used this on up to 250 table 10GB databases  on a fast server  and the response time is nothing short of amazing

User · Answer

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

[mysql] Search text in fields in every table of a MySQL database

Examples related to mysql