/*１。比较数据库表不一样的 select distinct a.id,d.name as tablename--,a.name as columename from [onwerbuild_wjl2].dbo.syscolumns a,[onwerbuild_wjl2].dbo.sysobjects d where d.xtype='u' and d.name not in (select c.name from [onwerbuild_wjl_m
/*１。比较数据库表不一样的
select distinct a.id,d.name as tablename--,a.name as columename
from [onwerbuild_wjl2].dbo.syscolumns a,[onwerbuild_wjl2].dbo.sysobjects d
where d.xtype='u' and 
d.name not in
(select c.name from [onwerbuild_wjl_m].dbo.syscolumns b,[onwerbuild_wjl_m].dbo.sysobjects c where c.xtype='u' and b.id=c.id)
 and a.id=d.id order by d.name
*/
--2.比较数据库字段
--查询库a比库b多了多少字段
--
use onwerbuild--库a
go
--删除所有数据
--建表
if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[test_del]') and objectproperty(id, n'isusertable') = 1)
drop table [dbo].[test_del]
go
create table [dbo].[test_del] (
 [tablename] [varchar] (100) collate chinese_prc_ci_as null ,
 [columename] [varchar] (100) collate chinese_prc_ci_as null
) on [primary]
go
delete from dbo.test_del
declare @table_name varchar(50)
declare abc cursor for
--先取出一个表
select d.name as tablename
from [onwerbuild_as].dbo.sysobjects d --库a
where d.xtype='u' order by d.name
open abc
fetch next from abc
into @table_name
while (@@fetch_status = 0)
begin
--一个表一个表的对应
--把不同的字段插入
use onwerbuild_as--库a
 insert into [onwerbuild].dbo.test_del
select d.name as tablename,a.name as columename
from [onwerbuild_as].dbo.syscolumns a,[onwerbuild_as].dbo.sysobjects d --库a
where d.name=@table_name 
and a.name not in
(select b.name from [onwerbuild].dbo.syscolumns b,[onwerbuild].dbo.sysobjects c where c.name=@table_name and b.id=c.id)--库b
 and a.id=d.id order by d.name
 fetch next from abc
 into @table_name
set nocount on
end
close abc
deallocate abc
select * from [onwerbuild].dbo.test_del
select distinct tablename from [onwerbuild].dbo.test_del
go