locked
SQL Query to union all table names on a database with MySQL RRS feed

  • Question

  • User-1453200658 posted

    Hi,

    I use this SQL query for find, in database MySql version 8.0.17, the tables whose name corresponds to the condition indicated in the clause LIKE

    SELECT table_name 
    FROM information_schema.TABLES 
    WHERE table_name LIKE 't#_contents#_s3%#_1#_2021' ESCAPE '#';
    
    +-------------------------+
    | TABLE_NAME              |
    +-------------------------+
    | t_contents_s300_1_2021  |
    | t_contents_s34d_1_2021  |
    | t_contents_s34g_1_2021  |
    | t_contents_s3sv_1_2021  |
    +-------------------------+
    4 rows in set (0.30 sec)
    

    My question is

    How to do go about for union all these tables returned by the SQL query ?

    Normally I would do this mode, but I don't know first all the names of the tables and the number of the tables returned by the SQL query... the names and the number of the tables it could be variable...

    I'm not the administrator of this database. This schema is a remote hosting where I can only read..

    SELECT
        `sUn`,
        `sUnName`,
        `contents` 'Set' 
    FROM
        `t_contents_s300_1_2021` UNION ALL
    SELECT
        `sUn`,
        `sUnName`,
        `contents` 'Set' 
    FROM
        `t_contents_s34d_1_2021` UNION ALL
    SELECT
        `sUn`,
        `sUnName`,
        `contents` 'Set' 
    FROM
        `t_contents_s34g_1_2021` UNION ALL
    SELECT
        `sUn`,
        `sUnName`,
        `contents` 'Set' 
    FROM
        `t_contents_s3sv_1_2021` 
    ORDER BY
        `sUn` ASC;
    

    Thanks in advance for any help.

    Thursday, February 18, 2021 5:08 AM

Answers

  • User753101303 posted

    Hi,

    You first query could generate a SELECT statement for each table ie  SELECT CONCAT('SELECT .. FROM ',table_name) etc... and then use StingJ.Join to concat those statements with "UNION ALL" in between.

    Or if you want to do all on the MySQL side you could sue GROUP_CONCAT to do the same thing and then  use EXECUTE to run the resulting statement.

    I understand you don't have control on the db but another option could be to expose that through a view '(that you'll need to update each year) unless tables are created in advance. 

    You have also partioned tables allowing to handle this kind of schema deisgn

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 18, 2021 8:39 AM