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"; -- Change this to your proc name
SET @output = "delimiter //";
OPEN appDBs;
REPEAT
FETCH appDBs INTO db;
IF NOT done THEN
-- Replace this procedure declaration with your procedure.
-- Make sure to keep the ',db,' syntax there.
-- You should really only have to change the parameters
-- and the stuff between the BEGIN and END clauses.
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 ;