Find and Replace on Database - MySQL

gistfile1.sql

BEGIN
    
    /* 
        PARAMETERS EXPECTED
        - IN p_db VARCHAR
        - IN p_from VARCHAR
        - IN p_to VARCHAR
    */

    DECLARE l_end INT DEFAULT 0;    
    DECLARE l_tablename VARCHAR(200);
    DECLARE l_columnname VARCHAR(200);
    
    /* 
        get a list of all the columns within the database insert into cursor
    */
    DECLARE c_columns CURSOR FOR
        SELECT table_name, column_name 
        FROM information_schema.columns 
        WHERE information_schema.columns.table_schema = p_db;
    
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET l_end = 1;

    /*
        step through cursor and check the tables for columns with the url in
    */
    OPEN c_columns;

        columns_loop: LOOP

            FETCH c_columns INTO l_tablename, l_columnname;

            IF l_end = 1 THEN
                LEAVE columns_loop;
            END IF;
            
            SET @d = CONCAT("UPDATE `",p_db,"`.",l_tablename," SET ",l_columnname," = REPLACE(",l_columnname,",\'",p_from,"\',\'",p_to,"\');");

            SET @sql = @d;
            PREPARE stat FROM @sql;
            EXECUTE stat;
            DEALLOCATE PREPARE stat;

        END LOOP columns_loop;

    CLOSE c_columns;
    

END