MySQL Search and Replace Stored Procedure

#### # CALL THIS SP USING THE FOLLOWING SYNTAX # sp_DbSearchReplace('oldText','NewText'); #### DROP PROCEDURE IF EXISTS `YOUR_DB_NAME_HERE`.`sp_DbSearchReplace`; DELIMITER $$ CREATE PROCEDURE `YOUR_DB_NAME_HERE`.`sp_DbSearchReplace` (IN oldString VARCHAR(255), IN newString VARCHAR(255)) BEGIN DECLARE nDone INT; DECLARE sTable VARCHAR(64); DECLARE sColumn VARCHAR(64); DECLARE cTables CURSOR FOR SELECT TABLE_NAME, COLUMN_NAME FROM information_schema.COLUMNS WHERE information_schema.COLUMNS.TABLE_SCHEMA = 'YOUR_DB_NAME_HERE'; DECLARE CONTINUE HANDLER FOR NOT FOUND SET nDone = 1; OPEN cTables; REPEAT FETCH cTables INTO sTable, sColumn; SET @sQuery = CONCAT('UPDATE ',sTable ,' SET ' ,sColumn, ' = REPLACE(',sColumn,',\'',oldString,'\',\'',newString,'\');'); PREPARE stmt FROM @sQuery; EXECUTE stmt; DEALLOCATE PREPARE stmt; UNTIL nDone = 1 END REPEAT; CLOSE cTables; END $$ DELIMITER ;

1 Response

thxxxxxxxxx u so much........

Write a comment

You can use [html][/html], [css][/css], [php][/php] and more to embed the code. Urls are automatically hyperlinked. Line breaks and paragraphs are automatically generated.