How to write a SQL query for multiple tables without join

Question

I have a database with ~ 1000 tables where each table has the column date. I want to see how many tables are between the date 2000 and 2016.

Each table is a company name, so that I get all the companies funded between 2000 and 2016 I would need this query.

I tried it with something like

SELECT * 
FROM date 
WHERE date BETWEEN 2000 AND 2020;

but the problem is, that I need to do it for all ~ 1000 tables.

Is there a way to write an easy loop for a SQL query?

Thanks for your help.

question from:https://stackoverflow.com/questions/65559788/how-to-write-a-sql-query-for-multiple-tables-without-join

Answer 1

SELECT *
FROM (
    SELECT CompanyName FROM table1
    UNION
    SELECT CompanyName FROM table2
)
WHERE date BETWEEN 2000 AND 2020

and dynamic query is below. You will keep all your table names in "yourTable".

Declare @ID int,
Declare @tableName varchar(50) 
Set @ID =1

While Exists(select * from yourTable where ID <=1000)
BEGIN
    Set @tableName = (Select tableName from yourTable where ID = @ID)

    Select CompanyName from @tableName 
    Where
    date BETWEEN 2000 AND 2020
    + ' UNION ALL '
    
    Set @ID = @ID +1

END