How to get data from different table having same column? I have a database which stores historical data in different tables with same column names. Is there any way I can query all tables and get result

Example

2016_01_01----Fields-f1,f2,f3,f4----Values-1,2,3,4
2016_02_01----Fields-f1,f2,f3,f4----Values-5,6,7,8
2016_12_01----Fields-f1-f2-f3-f4----Values-9,10,11,12


Result should be 

f1   f2    f4
1    2     4
5    6     8
9    10    12
UNION (Transact-SQL).


  Combines the results of two or more queries into a single result set
  that includes all the rows that belong to all queries in the union.


select T.f1, T.f2, T.f3
from [2016_01_01] as T
union all
select T.f1, T.f2, T.f3
from [2016_02_01] as T
union all
select T.f1, T.f2, T.f3
from [2016_12_01] as T;


One way to build the statement dynamically.

/*
-- Create tables
create table [2016-01-01](f1 int, f2 int, f3 int);
create table [2016-02-01](f1 int, f2 int, f3 int);
create table [2016-12-01](f1 int, f2 int, f3 int);
*/

declare @SQL nvarchar(max);

set @SQL = stuff((
  select 
N'
union all
select T.f1, T.f2, T.f3 
from ' + quotename(name) + N' as T '
  from sys.tables
  -- Get all tables where the name is a date
  where isdate(name) = 1 
  for xml path(''), type
  ).value('text()[1]', 'nvarchar(max)'), 1, 13, '');

-- Print statement
print @SQL;

-- Execute statment
exec (@SQL);