Our use of cookies

We use cookies to tailor your experience, gather analytics, and provide you with live assitance. By clicking "Accept" or continuing to browse our site you agree to the use of cookies. For more details please read our Cookie Policy.

Script: create a view of all common fields from multiple tables using TSQL

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]
            -- 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]
            [object_id] in
                select [table_id] from @tables t

declare @csvColNames as nvarchar(max) = convert(nvarchar(max),
    (select [column_name] as 'c'
        group by
            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
    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;

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


select * from [dbo].[All_Table_Union];