Stack multiple tables using UNION ALL
Here's a handy script that allows several tables with different schema to be merged into one. This greatly facilitates finding something the tables would have in common as all the rows are stacked together. For example, given log tables from several different applications that all use a similar login. Obviously the two things they have in common are LOGIN and DATESTAMP. This handy little script can pull all those tables together so you don't have to keep swapping between query results to find patterns.
There is one rule that must be followed: all columns in a specific position need to have the same data type. This may require all columns get converted to NVARCHAR or VARCHAR.
There are two parts for this script: the setup and the example. Basically, it creates all table and column combinations for the specified tables. Then, once it has that it scans over all the columns in each of the tables. If it find that combination of table and column it pulls that column, otherwise it creates a place holder for it.
This could easily be converted to a stored procedure.
Theoretically, this could work for any collection of tables so long as the total number of columns is manageable (insert Excel plugin here)
Thanks for reading.
Antony Liberato
Sr. Database Administrator
--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! 
