--将A表的所有字段名塞进临时表#temp_AColumns中 select b.name into #temp_AColumns from sys.objects a inner join sys.columns b on a.object_id =b.object_id where a.name ='A'--将A表的所有字段名塞进临时表#temp_BColumns中 select b.name into #temp_BColumns from sys.objects a inner join sys.columns b on a.object_id =b.object_id where a.name ='B' --将两个临时表中相同字段值select出来 select * from #temp_AColumns a inner join #temp_BColumns b on a.name=b.name
SELECT * INTO student1 FROM dbo.student WHERE 1<>1 --创建相同的表结构ALTER TABLE student DROP COLUMN sage -- 删除原表的sage ALTER TABLE dbo.student ADD age INT--添加一列age SELECT name,COUNT(name) FROM ( select name from syscolumns where id=object_id('student') UNION ALL select name from syscolumns where id=object_id('student1') )a GROUP BY a.name HAVING COUNT(a.name)<2 --可能有更简单的方法..下班了..闪人了..美女
谢谢大家,解决了。 select name from syscolumns where id=object_id('tableA') and name not in (select name from syscolumns where id=object_id('tableB'))
select b.name into #temp_AColumns from sys.objects a inner join sys.columns b
on a.object_id =b.object_id
where a.name ='A'--将A表的所有字段名塞进临时表#temp_BColumns中
select b.name into #temp_BColumns from sys.objects a inner join sys.columns b
on a.object_id =b.object_id
where a.name ='B'
--将两个临时表中相同字段值select出来
select * from #temp_AColumns a inner join #temp_BColumns b
on a.name=b.name
ALTER TABLE dbo.student ADD age INT--添加一列age
SELECT name,COUNT(name) FROM
(
select name from syscolumns where id=object_id('student')
UNION ALL
select name from syscolumns where id=object_id('student1')
)a
GROUP BY a.name
HAVING COUNT(a.name)<2
--可能有更简单的方法..下班了..闪人了..美女
select name from syscolumns where id=object_id('tableA')
and name not in (select name from syscolumns where id=object_id('tableB'))