求多个表的列差异

[Weaseatiti]

prodtype_1
列：
ID name1 name2 name3

prodtype_2
列：
ID name1 name2


prodtype_3
列：
ID name4

prodtype4
ID name1 name2 name3 name4
-------------------------------------------------------
求得
prodtype_1
name4

prodtype_2
name3 name4

prodtype_3
name1 name2 name3

prodtype4


谢谢

[zjcxc]

with a as(
select distinct name from sys.columns
where object_name(object_id) in ('prodtype_1', 'prodtype_2', 'prodtype_3', 'prodtype_4')
)
select 'prodtype_1', name from a
where name not in(
	select name from sys.columns
	where object_name(object_id) = 'prodtype_1'
)
union all
select 'prodtype_2', name from a
where name not in(
	select name from sys.columns
	where object_name(object_id) = 'prodtype_2'
)
union all
select 'prodtype_3', name from a
where name not in(
	select name from sys.columns
	where object_name(object_id) = 'prodtype_3'
)
union all
select 'prodtype_4', name from a
where name not in(
	select name from sys.columns
	where object_name(object_id) = 'prodtype_4'
)

[Weaseatiti]

引用 1 楼 zjcxc 的回复:

with a as(
select distinct name from sys.columns
where object_name(object_id) in ('prodtype_1', 'prodtype_2', 'prodtype_3', 'prodtype_4')
)
select 'prodtype_1', name from a
where name not in(
	select name from sys.columns
	where object_name(object_id) = 'prodtype_1'
)
union all
select 'prodtype_2', name from a
where name not in(
	select name from sys.columns
	where object_name(object_id) = 'prodtype_2'
)
union all
select 'prodtype_3', name from a
where name not in(
	select name from sys.columns
	where object_name(object_id) = 'prodtype_3'
)
union all
select 'prodtype_4', name from a
where name not in(
	select name from sys.columns
	where object_name(object_id) = 'prodtype_4'
)

大神。。这个表是动态的怎么做？ 比如说 表名不定是4，可能是5 但是命名规则差不多