SQL select records from an arbitrary number of identical tables

Question

I am trying to query a database (SQLServer) with multiple tables of identical structure and with related names i.e.

[TABLE 01 $TRANSACTIONS]
[TABLE 02 $TRANSACTIONS]
...
[TABLE (n) $TRANSACTIONS]

I have a query returning the desired records on one table at a time and can manually select multiple tables with

SELECT {QUERY01} FROM [TABLE 01 $TRANSACTIONS]
UNION
SELECT {QUERY02} FROM [TABLE 02 $TRANSACTIONS]
...
SELECT {QUERY(n)} FROM [TABLE (n) $TRANSACTIONS]

The generic query into which I substitute the required table name is approx 200 lines, involving a ROWNUMBER()/PARTITION BY function, multiple joins to related tables and some ordering.

Over time, new tables will be added and n will change.

Can anyone suggest a way to select the UNION of records from all n tables for arbitrary values of n?

Note: the list of n tables can be easily obtained with a query on the sysobjects table

SELECT Name FROM sysobjects
WHERE Type = 'U'
AND Name LIKE '%$TRANSACTIONS'
ORDER BY Name

Answer 1

AFAIK, your best bet is to use your sysobjects query to generate a new view definition periodically.

You might be able to create a DDL trigger which runs the procedure to re-generate this view when your tables change -- I don't really know. DB designs like this are a trainwreck.

Answer 2

You could cursor through your sysobjects query and construct the sql statement. Then you can call sp_executesql to run it. From experience, I can tell you that these are a pain in the A$$ to debug. I would also expect this solution to fall apart with any vendor upgrade. Good luck.