SQL Server中多个表格求出相同列和不同列（答案来自CSDN上SQL专家的回答）

从不同表格中求出所有列名相同的列（不考虑类型和精度等）,注意最后一列的数字3，是查询的表格数目
select a.name ,count(1) as tt from syscolumns a,sysobjects b
where a.id=b.id and b.xtype='u' and b.name in ('table_1','table_2','table_3')
group by a.name
having count(1)=3;

从不同表格中求出相同列后，每个表格去除掉这些相同列后返回的结果

create view Test
as
select a.name ,count(1) as tt from syscolumns a,sysobjects b
where a.id=b.id and b.xtype='u' and b.name in ('table_1','table_2','table_3')
group by a.name
having count(1)=3;
go
select  a.name as 列名
from syscolumns a,sysobjects b
where b.xtype='u' and a.id=b.id  and b.name in ('table_1') and a.name not in (select name from Test)
group by b.name,a.name having count(1)=1

drop view Test


原帖地址：如何查询出N张表中的相同列和每个表各自的不同列（N不定且表结构不能预知）

转载于:https://www.cnblogs.com/Cindy_weiwei/archive/2009/08/06/1540590.html