CODEWITHSUNDEEP
mysql
Alec Smart
Alec Smart

Reputation: 95980

mySQL query across multiple schemas

I have a multi-schema structure (same table across all schemas).

Here is how I can fetch all the schemas:

select table_schema from information_schema.tables where table_name = 'messages' and TABLE_TYPE = 'BASE TABLE'

This query is for a join between 'messages' table across two tables-

select *, '1' as customer from customer_1.messages union select *,  '2' as customer from customer_2.messages

How do I use the perform the above query for all the schemas from the first query? Need not be a join. Could be anything that helps me return the results across all schemas.

Note: There could be 5000-10000 schemas

Upvotes: 1

Views: 1855

Answers (1)

Akina
Akina

Reputation: 42844

DELIMITER @@;

CREATE PROCEDURE join_all_data()
BEGIN
    SET @counter := 0;
    SELECT GROUP_CONCAT('select *, ',
                        @counter := @counter + 1,
                        ' as customer from ',
                        table_schema,
                        '.messages'
                        SEPARATOR ' UNION ALL ')
        FROM information_schema.tables 
        WHERE table_name = 'messages' 
          AND table_type = 'BASE TABLE'
        INTO @sql;
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DROP PREPARE stmt;
END
@@;

DELIMITER ;

CALL join_all_data;

Upvotes: 2

Related Questions