SQL Query to union all table names on a database with MySQL[Answered] RSS

• • Reply
  Edward Sheri...

  Member

  25 Points

  39 Posts

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

  Feb 18, 2021 05:08 AM|Edward Sheriff Curtis|LINK

  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.

• • Reply
  PatriceSc

  All-Star

  48530 Points

  18079 Posts

  Re: SQL Query to union all table names on a database with MySQL

  Feb 18, 2021 08:39 AM|PatriceSc|LINK

  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