Stored Procedure that will generate a view with dynamic unions

stored_proc.sql

CREATE PROCEDURE `dyn_view_maker`()
 READS SQL DATA
BEGIN
DECLARE schemaName VARCHAR(64); 
 DECLARE tablesName VARCHAR(64);
DECLARE done INT DEFAULT FALSE; 
 DECLARE cursor1 CURSOR FOR 
 SELECT table_schema, table_name 
 FROM information_schema.TABLES
 WHERE table_schema = ‘your schema name’ 
 AND table_name LIKE ‘table_temp_2_%’;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
SET @queryString = ‘’;
OPEN cursor1;
read_loop: LOOP — loop over the rows returned by cursor
 FETCH cursor1 INTO schemaName, tablesName; 
 IF done THEN
 LEAVE read_loop;
 END IF;
IF @queryString = ‘’ THEN
 SET @queryString := CONCAT(‘CREATE OR REPLACE VIEW v_your_view name AS SELECT … FROM `’, schemaName, ‘`.`’, tablesName, ‘` JOIN … AS … WHERE …’);
 ELSE
 SET @queryString := CONCAT(@queryString, ‘ UNION ALL SELECT … FROM `’, schemaName, ‘`.`’, tablesName, ‘` JOIN … AS … WHERE …’);
 END IF;
 END LOOP;
 CLOSE cursor1;
select @queryString;
PREPARE stmt FROM @queryString;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt; 
 
END