/SQL/Mysql loop through databases and execute query

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 ;

Source: stackoverflow.com

Similar codes

7 votes239 views3 answers
Select latest entry in sql table
SQL
0 vote249 views1 answer
How to connect to xampp sql server on windows cmd
SQL
13 votes221 views2 answers
Display users in mysql
SQL
1 vote245 views2 answers
Oracle difference between two dates in seconds
SQL
0 vote238 views1 answer
Postgresql using reserved word as column name
SQL
6 votes246 views3 answers
[2021-10-05T13:43:48.961Z] error Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client
SQL
2 votes249 views1 answer
Java.lang.arrayindexoutofboundsexception: index 0 out of bounds for length 0
SQL
0 vote226 views1 answer
Reading from right to left from string find first special characters in sql
SQL
-1 vote245 views3 answers
Compute age mysql
SQL
0 vote230 views1 answer
Add sqlite3 in lumen
SQL