Disclaimer: this script is given as is, we do not support it nor take responsibility for any use you make of it.

The script below creates a view that includes multiple table and selects all common fields across those tables.

This can be useful when you have many tables with similar structure and you want to query them as one single source of data.


declare @tables table ([table_id] int, [table_name] nvarchar(1000))

insert into @tables ([table_id], [table_name])
    select [object_id], [name]
        from
            sys.tables
        where
            -- filter here the tables you want to include in the union view
            [name] like 'my_prefix%';

declare @tableCount as int = (select count(1) from @tables)

declare @allColumns table ([table_id] int, [column_name] nvarchar(1000))

insert into @allColumns ([table_id], [column_name])
    select [object_id], [name]
        from
            sys.columns
        where
            [object_id] in
            (
                select [table_id] from @tables t
            );

declare @csvColNames as nvarchar(max) = convert(nvarchar(max),
    (select [column_name] as 'c'
        from
            @allColumns
        group by
            [column_name]
        having
            count(1) = @tableCount
        for xml path(''), type
    ));

set @csvColNames = replace(@csvColNames, '</c><c>', '], [');
set @csvColNames = replace(@csvColNames, '<c>', '[');
set @csvColNames = replace(@csvColNames, '</c>', ']');

declare tableCurs cursor for
    select [table_name] from @tables order by [table_name];

declare @table_name as nvarchar(1000);

open tableCurs;

fetch next from tableCurs into @table_name;

declare @createViewSQL as nvarchar(max) = '';
declare @nl as char(2) = char(13) + char(10);

while @@fetch_status = 0
begin
   
    if @createViewSQL != '' set @createViewSQL = @createViewSQL + 'union' + @nl;

    set @createViewSQL = @createViewSQL + 'select [table_name] = ''' + @table_name + ''', ' + @csvColNames + ' from [' + @table_name + ']' + @nl;

    fetch next from tableCurs into @table_name;
end

close tableCurs;
deallocate tableCurs;

-- choose your name here for the [All_Table_Union] view
set @createViewSQL = 'create view [dbo].[All_Table_Union] as ' + @nl + @createViewSQL;

print @createViewSQL

exec(@createViewSQL)

select * from [dbo].[All_Table_Union];