mysql loop through databases and execute query code example
Example: mysql loop through databases and execute query
delimiter
DROP PROCEDURE IF EXISTS create_procedures
CREATE PROCEDURE create_procedures()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE db VARCHAR(255);
DECLARE appDBs CURSOR FOR SELECT schema_name FROM information_schema.schemata WHERE schema_name LIKE 'application_%';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET @procName = "simpleproc";
SET @output = "delimiter //";
OPEN appDBs;
REPEAT
FETCH appDBs INTO db;
IF NOT done THEN
SET @output = CONCAT(@output,'
DROP PROCEDURE IF EXISTS ',db,'.',@procName,'//
CREATE PROCEDURE ',db,'.',@procName,'()
BEGIN
SELECT 1;
END//');
END IF;
UNTIL done END REPEAT;
CLOSE appDBs;
SET @output = CONCAT(@output,'\ndelimiter ;');
SELECT @output AS procs;
END
delimiter ;