Select * from n tables

Is there a way to write a query like:

select * from <some number of tables>

...where the number of tables is unknown? I would like to avoid using dynamic SQL. I would like to select all rows from all the tables that (the tables) have a specific prefix:

select * from t1
select * from t2
select * from t3
...

I don't know how many t(n) might there be (might be 1, might be 20, etc.) The t table column structures are not the same. Some of them have 2 columns, some of them 3 or 4.

It would not be hard using dynamic SQL, but I wanted to know if there is a way to do this using something like sys.tables.

UPDATE

开发者_如何学Go

Basic database design explained

N companies will register/log in to my application

Each company will set up ONE table with x columns

(x depends on the type of business the company is, can be different, for example think of two companies: one is a Carpenter and the other is a Newspaper)

Each company will fill his own table using an API built by me

What I do with the data:

I have a "processor", that will be SQL or C# or whatever.

If there is at least one row for one company, I will generate a record in a COMMON table.

So the final results will be all in one table.

Anybody from any of those N companies will log in and will see the COMMON table filtered for his own company.

---

There would be no way to do that without Dynamic SQL. And having different table structures does not help that at all.

Update

There would be no easy way to return the desired output in one single result set (result set would have at least the same # of columns of the table with most columns and don't even get me started on data types compatibility).

However, you should check @KM.'s answer. That will bring multiple result sets.

---

to list ALL tables you could try :

EXEC sp_msforeachtable 'SELECT * FROM  ?'

you can programmability include/exclude table by doing something like:

EXEC sp_msforeachtable 'IF LEFT(''?'',9)=''[dbo].[xy'' BEGIN SELECT * FROM  ? END ELSE PRINT LEFT(''?'',9)'