Tuesday, March 3, 2015

serach 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
 

No comments:

Post a Comment

web stats