declare @sqlstr varchar(8000),
@dtemp1 varchar(100),
@dtemp2 varchar(100),
@dtemp3 varchar(100),
@dtemp4 varchar(100),
@dtemp5 varchar(100),
@dtemp6 varchar(100),
@dtemp7 varchar(100)
--匹配数据库1中的表，在数据库2中对应的相同表，他们的字段是否匹配
--清除临时表
if object_id('tempdb..#db1') is not null drop table #db1
if object_id('tempdb..#db2') is not null drop table #db2
if object_id('tempdb..#db2a') is not null drop table #db2a
if object_id('tempdb..#db3') is not null drop table #db3
--将数据库Northwind 字典信息（表名，字段名，字段类型名）插入临时表#db1
select a.name tname,
b.name cname,
c.name tpname,
COLUMNPROPERTY(b.id, b.name, 'PRECISION') tplength,
isnull(COLUMNPROPERTY(b.id, b.name, 'Scale'), 0) tpdec,
(case
when b.isnullable = 1 then
'null'
else
'not null'
end) tpisnull,
isnull(d.text, '') tpdefault
into #db1
from Northwind .. sysobjects a, Northwind .. syscolumns b, Northwind .. systypes c,Northwind..syscomments d
where a.id = b.id
and a.xtype = 'U'
and b.xusertype = c.xusertype
and b.cdefault *= d.id
--将数据库pubs 字典信息（表名，字段名，字段类型名）插入临时表#db2
select a.name tname,
b.name cname,
c.name tpname,
COLUMNPROPERTY(b.id, b.name, 'PRECISION') tplength,
isnull(COLUMNPROPERTY(b.id, b.name, 'Scale'), 0) tpdec,
(case
when b.isnullable = 1 then
'null'
else
'not null'
end) tpisnull,
isnull(d.text, '') tpdefault
into #db2
from pubs .. sysobjects a, pubs .. syscolumns b, pubs .. systypes c,pubs..syscomments d
where a.id = b.id
and a.xtype = 'U'
and b.xusertype = c.xusertype
and b.cdefault *= d.id
--检查两数据库中的表的数目是否相同
select count(*) tablecounts from (select distinct tname from #db1) T1
select count(*) tablecounts from (select distinct tname from #db2) T2
--数目不相同，则只找出#db2在#db1中，相同表的记录，并插入到临时表#db2a
select tname,cname,tpname,tplength,tpdec,tpisnull,tpdefault
into #db2a
from #db2 a
where EXISTS(select 1 from #db1 b where a.tname=b.tname )
--检查两数据库中的表的数目
select count(*) tablecounts from (select distinct tname from #db1) T1
select count(*) tablecounts from (select distinct tname from #db2a) T2
--将#db1中存在的表，与#db2中相同表进行比较，比较他们的字段是否相同
--查出的结果为在数据库1（#db1）中，需要增加的字段
select * into #db3 from #db2a a
where not EXISTS(select 1 from #db1 b where (b.tname+b.cname +b.tpname)=(a.tname+a.cname +a.tpname) )
--===============================================================
--创建游标
declare cs_db3 cursor for
select tname,cname,tpname,tplength,tpdec,tpisnull,tpdefault from #db3
open cs_db3
fetch cs_db3 into @dtemp1,@dtemp2,@dtemp3,@dtemp4,@dtemp5,@dtemp6,@dtemp7
while @@fetch_status=0
begin
select @sqlstr=' '
select @sqlstr=@sqlstr+' alter table '+@dtemp1+' add ' +@dtemp2 + ' ' + @dtemp3
exec(@sqlstr)
fetch cs_db3 into @dtemp1,@dtemp2,@dtemp3,@dtemp4,@dtemp5,@dtemp6,@dtemp7
end
close cs_db3
deallocate cs_db3
= You Jack w
p Users + Jack Ca%D3 Jack C6%B Jack a You 5 Smartvehiclefinance . Smartvehiclefinance omr Smartvehiclefinance hsearchs Jack awww.ai222.comc Users ysearchS Smartvehiclefinance a Smartvehiclefinance t Users e Smartvehiclefinance i Users l You f Smartvehiclefinance n You n Users e Smartvehiclefinance 7www.crsoso.info o Users 2sesearchrsearchhc Smartvehiclefinance S Jack artsearche You i You l
fsearchnsearchnce Jack mou You = Users U Smartvehiclefinance ewww.qingren55.coms search= Smartvehiclefinance = Jack = You = Users =search=search=search= Smartvehiclefinance = Jack ==search= Users = Smartvehiclefinance = Users = You =
== You =
=www.aaa258.con= Users = Jack = You = You =search= Smartvehiclefinance =