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

Databases
1

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.

2

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

3

thanks for reply.

i use language ASP NET C#

do you mean get individual tables into an array?

do you have any example please?

4

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)
5

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. :slight_smile:

1 Like
6

Thanks a lot for this help

7

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.

8

thanks for your interest

I have resolved using server side language

9

As long as ā€œit worksā€ right?

10

where to duplicate the tables?

11

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.