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

[comunidadmexicanarom]

Hi there,

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

mysql> 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.

[igor_g]

You can’t to do this directly with single SQL-query. Use some script language for sequential queries.

[comunidadmexicanarom]

thanks for reply.

i use language ASP NET C#

do you mean get individual tables into an array?

do you have any example please?

[comunidadmexicanarom]

hi all,

These are tables list on database MySql version 8.0.17

t_contents_s300_10_2021
t_contents_s300_1_2021
t_contents_s300_2_2021
t_contents_s300_3_2021
t_contents_s34d_1_2021
t_contents_s34g_1_2021
t_contents_s34g_2_2021
t_contents_s3sv_1_2021
t_contents_s3sv_2_2021

I need find on these list of tables all table like s3 and 1 and 2021

I have tried this query but the return contains all numbers and not only 1

How to do resolve this?

Thanks in advance for any help.

mysql> SELECT
	table_name 
FROM
	information_schema.TABLES 
WHERE
	table_name LIKE ( 't_contents_s3%' );
+-------------------------+
| TABLE_NAME              |
+-------------------------+
| t_contents_s300_10_2021 |
| t_contents_s300_1_2021  |
| t_contents_s300_2_2021  |
| t_contents_s300_3_2021  |
| t_contents_s34d_1_2021  |
| t_contents_s34g_1_2021  |
| t_contents_s34g_2_2021  |
| t_contents_s3sv_1_2021  |
| t_contents_s3sv_2_2021  |
+-------------------------+
9 rows in set (0.44 sec)

[Martyr2]

Yeah the reason it is returning all tables is because t_contents_s3 is the start of all the table names. So you need to get a little more specific. You can use wildcards in the middle (and even multiple wildcards) so maybe try something like…

select table_name from information_schema.TABLES where table_name LIKE ('t_contents_s3%_1_2021')

This should give you all tables that have s3, 1, and 2021 in the name. See if this achieves what you are looking to do.

[comunidadmexicanarom]

@Martyr2

select table_name from information_schema.TABLES where table_name LIKE (‘t_contents_s3%_1_2021’)

Thanks a lot for this help

[benanamen]

Why are you (what appears to be) duplicating tables? What is the real problem you are trying to solve by doing this? I suspect you also have a DB design problem. Please provide a high level overview of what you have going on.

[comunidadmexicanarom]

thanks for your interest

I have resolved using server side language

[benanamen]

As long as “it works” right?

[comunidadmexicanarom]

where to duplicate the tables?