--CREATE EXAMPLE TABLES
create table [dbo].[table1] ([name1] varchar(100), [name2] varchar(100), [name3] varchar(100))
create table [dbo].[table2] ([name1] varchar(100), [name2] varchar(100), [name3] varchar(100), [name5] varchar(100), [name6] varchar(100))
create table [dbo].[table3] ([name1] varchar(100), [name3] varchar(100), [name5] varchar(100), [name7] varchar(100))
create table [dbo].[table4] ([name4] varchar(100))
--THEN INSERT DATA
insert into [dbo].[table1] ([name1], [name2], [name3])
values('T1N1','T1N2','T1N3')

insert into [dbo].[table2] ([name1], [name2], [name3], [name5], [name6])
values('T2N1','T2N2','T2N3','T2N5','T2N6')

insert into [dbo].[table2] ([name1], [name2], [name3], [name5], [name6])
values('T2N1','T2N2','T2N3','T2N5','T2N6')

insert into [dbo].[table3] ([name1], [name3], [name5], [name7])
values('T3N1','T3N3','T3N5','T3N7')

insert into [dbo].[table4] ([name4])
values('T4N4')

--get the table names, exclude the "UNION ALL" command on the first table. this is the first step in preparing the result script.
;with [tables]
as
(
select
case when tid>1 then 'union all' else '' end as union_all
,table_name
from
(
select
table_schema + '.' + table_name as table_name, row_number() over (order by table_name) as tid
from information_schema.tables
where table_name like 'table_'
) AS A
)
--Then get to cooresponding columns from the same set of tables
, [columns]
as
(
select table_schema + '.' + table_name as table_name, column_name
from information_schema.columns
where table_name like 'table_'
)
,combinations
as
(
select distinct [tables].table_name, [columns].column_name from [tables],[columns]
)
--then find where the place holders need to be.
, [placeholders]
as
(
select
tc.table_name, tc.column_name, iif(b.column_name is null, ''''' as [' + tc.column_name + ']', b.column_name) as mapper
,row_number() over (partition by tc.table_name order by tc.column_name) as colide
from
combinations tc
left join
[columns] as b
on tc.table_name = b.table_name and tc.column_name = b.column_name
)
--keep the values in [placeholder]; store them in a temp table
select * into #placeholders from [placeholders]

--for the columns that don't exist in any of the tables, the mapper column shows '' as []
--now we just turn this table into a script using STUFF FOR XML().
--we'll use the same CTE as before, though this could be saves as a table, temp or otherwise.

declare @script nvarchar(4000) = N''
;with [tables]
as
(
select
case when tid>1 then 'union all' else '' end as union_all
,table_name
from
(
select
table_schema + '.' + table_name as table_name, row_number() over (order by table_name) as tid
from information_schema.tables
where table_name like 'table_'
) AS A
)
select @script = @script + union_all + char(10) + 'select ''' + t.table_name + ''' as [table], ' +
stuff((select ', ' + m.mapper from #placeholders m where m.table_name = t.table_name for xml path('')),1,1,'') + ' from ' + table_name + char(10) + char(13)
from [tables] t
print @script
exec sp_executesql @script
--done!