UNION ALL to combine data across companies, with tables of same expression

Question

Is there by any chance a possibility to combine/union data from multiple tables, ending on the same characters in a SELECT statement?

We have several companies, built up with the same tables, and also the same column setup within the tables. For me to not making a UNION all statement, that includes all of the stores, I would like to know, if I could make a script, which does this for me.

Example of tables:

[Database].[dbo].[Company1$Sales Line]
[Database].[dbo].[Company2$Sales Line]
[Database].[dbo].[Company3$Sales Line]...

How I write the script today:

SELECT *
FROM [Database].[dbo].[Company1$Sales Line]
UNION ALL
SELECT *
FROM [Database].[dbo].[Company2$Sales Line]
UNION ALL
SELECT *
FROM [Database].[dbo].[Company3$Sales Line]...

I would think that there is an easier solution to do this. Could probably be a WHILE loop statement - but I have no idea, what is the best practice, and if it's even possible. Otherwise I should make a VBA in Excel to assist me in doing this.

Thanks in advance :)

Answer 1

If you're after a way to generate the SQL you need on the fly, you can use the following as a basis, perhaps to build the content of a view which you can then query. Also note that generally, you don't specify 3 part names as you connect to the database in question. It's different of course for multiple databases, but that doesn't seem to be the case given your sample data.

select String_Agg(Concat('select * from ', QuoteName(Schema_Name(schema_id)), '.', QuoteName(name)), ' union all' + Char(13))
from sys.tables
where schema_id=Schema_Id('dbo') and name like 'Company%$Sales Line';