比较两个类似数据库中的各个用户表的思路

分为A，B两个数据库

1、查找A中存在B中不存在的表*

2、查找B中存在A中不存在的表

3、查找A、B中都存在的表并比较表的列

 

--差集表

select 'A' flag,name tablename, 'table' mark into #table from A.dbo.sysobjects where type='U' and name not exists(select name from B.dbo.sysobjects where type='U')

union all

select 'B' flag,name tablename, 'table' mark from B.dbo.sysobjects where type='U' and name not exists(select name from A.dbo.sysobjects where type='U')

 

--交集表

2、select a.* into #comm from A.dbo.sysobjects a, B.dbo.sysobjects b where a.type='U' and b.type='U' and a.name=b.name

--A中交集表的列

select a.name,b.name tablename into #a from A.dbo.syscolumns a, #comm b a.id=object_id('A..'+b.name) order by b.name

--B中交集表的列

select a.name, b.name tablename into #b from B.dbo.syscolumns a, #comm b a.id=object_id('B..'+b.name) order by b.name

--A，B中交集表不同的列

select * into #field from (

select 'p' flag,a.tablename,'' mark from #a a where not exists(select * from #b where a.tablename=tablename and a.name=b.name)

union all

select 'p' flag,a.tablename,'' mark from #b a where not exists(select * from #a where a.tablename=tablename and a.name=b.name)

) a

 

select * from #table

union all

select * from #field

drop table #table

drop table #field

drop table #comm

drop table #a

drop table #b