Remove From My Forums

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

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

0

Sign in to vote
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

Answered by:

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

Question

Answers