How can I Search for a string in all fields across all tables of a MySQL database?

Question

I want to find a string and I don't know exactly which column and which table.

How can I find it?

Thanks.

Answer 1

This is a grueling problem and I have grueling suggestions

SUGGESTION #1: Use information_schema

See my old posts on how to go about doing this

• Jul 19, 2012 : Query to find and replace text in all tables and fields of a mysql db
• Mar 19, 2013 : How to search whole MySQL database for a particular string

SUGGESTION #2: Use mysqldump

You should be able to hunt down the string using mysqldump

Let's say the string you are looking for is 'Hello, World'

You can mysqldump all user-defined databases into a text file and grep the text file:

STRTOFIND="Hello, World"
DUMPFILE=mysqldump_to_search.sql
SRCHFILE=Search_Resutls.txt
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SET group_concat_max_len = 1048576;"
SQL="${SQL} SELECT GROUP_CONCAT(schema_name SEPARATOR ' ')"
SQL="${SQL} FROM information_schema.schemata WHERE schema_name NOT IN"
SQL="${SQL} ('information_schema','performance_schema','mysql')"
DBLIST=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
MYSQLDUMP_OPTIONS="--single-transaction --routines --triggers -B ${DBLIST}"
mysqldump ${MYSQL_CONN} -A ${MYSQLDUMP_OPTIONS} > ${DUMPFILE}
grep -n "${STRTOFIND}" ${DUMPFILE} > ${SRCHFILE}

The search results file will not only have the line the string is located, but also the linenumber within the dump file.

GIVE IT A TRY !!!

Answer 2

If you want to do it in pure MySQL SP, you could use this:

    CREATE TABLE `temp_details` (
     `t_schema` varchar(45) NOT NULL,
     `t_table` varchar(45) NOT NULL,
     `t_field` varchar(45) NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

    ## Procedure for search in all fields of all databases
    DELIMITER $$
    #Script to loop through all tables using Information_Schema
    DROP PROCEDURE IF EXISTS get_table $$
    CREATE PROCEDURE get_table(in_search varchar(50))
     READS SQL DATA
    BEGIN
     DECLARE trunc_cmd VARCHAR(50);
     DECLARE search_string VARCHAR(250);

     DECLARE db,tbl,clmn CHAR(50);
     DECLARE done INT DEFAULT 0;
     DECLARE COUNTER INT;

     DECLARE table_cur CURSOR FOR
     SELECT concat('SELECT COUNT(*) INTO @CNT_VALUE FROM `',table_schema,'`.`',table_name,'` WHERE `', column_name,'` REGEXP ''',in_search,''';')
     ,table_schema,table_name,column_name
     FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA NOT IN ('information_schema','test','mysql');

     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

     #Truncating table for refill the data for new search.
     PREPARE trunc_cmd FROM "TRUNCATE TABLE temp_details;";
     EXECUTE trunc_cmd ;

     OPEN table_cur;
     table_loop:LOOP
     FETCH table_cur INTO search_string,db,tbl,clmn;

     #Executing the search
     SET @search_string = search_string;
     SELECT search_string;
     PREPARE search_string FROM @search_string;
     EXECUTE search_string;


     SET COUNTER = @CNT_VALUE;
     SELECT COUNTER;

     IF COUNTER>0 THEN
     # Inserting required results from search to table
     INSERT INTO temp_details VALUES(db,tbl,clmn);
     END IF;

     IF done=1 THEN
     LEAVE table_loop;
     END IF;
     END LOOP;
     CLOSE table_cur;

     #Finally Show Results
     # SELECT * FROM temp_details;
    END $$
    DELIMITER ;

At the end you just run: select * from [SCHEMA].temp_details;

PD: The time for this procedure to exit depends of the database data volume.