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];