Search String in MYSQL

to search string from mysql database from all tables, than here we have procedure for it. Execute below two queries,
1. create table, which will store the result into it
2. execute procedure on database, from which you want to search string.


create table temp_details (t_db varchar(50), t_table varchar(50) , t_field varchar(50));

DELIMITER $$

DROP PROCEDURE IF EXISTS search_string $$

CREATE PROCEDURE search_string(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 IN ('schema_name');
    
    # note change schema name to present schema name
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
    
    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 concat("SELECT * FROM ",t_table, " WHERE ", t_field, " REGEXP '", in_search, "';") FROM temp_details;
END $$
DELIMITER ;

call search_string('abc');

it will return some set of queries, which we can directly execute to check string respective value in table.

related link:

SEARCH STRING IN POSTGRES

http://j4info.blogspot.in/2014/10/string-search-in-database-postgres.html