MySQL: Query tables across multiple databases

March 3, 2018

Posted in:

DROP PROCEDURE IF EXISTS `MultiDBQuery`;
DELIMITER $$

CREATE PROCEDURE `MultiDBQuery`(
	IN param_table_name VARCHAR(255) CHARACTER SET utf8
)
BEGIN

    DECLARE database_name   VARCHAR(250);
    DECLARE exit_loop       BOOLEAN;
    DECLARE union_query     TEXT DEFAULT '';

    DECLARE all_databases CURSOR FOR 
        SELECT DISTINCT `table_schema`
        FROM `information_schema`.`tables`
        WHERE `table_name` = param_table_name;

    DECLARE CONTINUE HANDLER
        FOR NOT FOUND SET exit_loop = TRUE;

    OPEN all_databases;

    get_database: LOOP
        FETCH all_databases INTO database_name;

        IF exit_loop THEN
            -- remove trailing UNION ALL statement
            SET union_query = TRIM(TRAILING ' UNION ALL ' FROM union_query);
            LEAVE get_database;
        END IF;

	SET union_query = concat(union_query, 'SELECT * FROM `', database_name, '`.`', param_table_name, '` UNION ALL ');

      END LOOP get_database;

    CLOSE all_databases;

    SET @final_query = concat('SELECT * FROM (', union_query, ') AS results;');
    PREPARE statement1 FROM @final_query;   
    EXECUTE statement1;
    DEALLOCATE PREPARE statement1;

END$$

DELIMITER ;

CALL MultiDBQuery('your_table_name');

Adapted from https://stackoverflow.com/a/22500171

Tags

Return to blog