//http://www.rzrgm.cn/Leo_wl/p/11069813.html

/*
    使用說(shuō)明：Old數(shù)據(jù)庫(kù)為DB_V1，New數(shù)據(jù)庫(kù)為[localhost].DB_V2。根據(jù)實(shí)際需要批量替換數(shù)據(jù)庫(kù)名稱(chēng)
    腳本來(lái)源：http://www.rzrgm.cn/zhang502219048/p/11028767.html
*/

-- sysobjects插入臨時(shí)表
select s.name + '.' + t.name as TableName, t.* into #tempTA 
from DB_V1.sys.tables t
inner join DB_V1.sys.schemas s on s.schema_id = t.schema_id

select s.name + '.' + t.name as TableName, t.* into #tempTB 
from [localhost].DB_V2.sys.tables t
inner join [localhost].DB_V2.sys.schemas s on s.schema_id = t.schema_id

-- syscolumns插入臨時(shí)表
select * into #tempCA from DB_V1.dbo.syscolumns 
select * into #tempCB from [localhost].DB_V2.dbo.syscolumns

-- 第一個(gè)數(shù)據(jù)庫(kù)表和字段 
select b.TableName as 表名, a.name as 字段名, a.length as 長(zhǎng)度, c.name as 類(lèi)型
into #tempA
from #tempCA a
inner join #tempTA b on b.object_id = a.id
inner join systypes c on c.xusertype = a.xusertype
order by b.name 
-- 第二個(gè)數(shù)據(jù)庫(kù)表和字段 
select b.TableName as 表名, a.name as 字段名, a.length as 長(zhǎng)度, c.name as 類(lèi)型
into #tempB
from #tempCB a
inner join #tempTB b on b.object_id = a.id
inner join systypes c on c.xusertype = a.xusertype
order by b.name

--刪掉的字段
select * from    
( 
    select * from #tempA
    except
    select * from #tempB
) a;

--增加的字段
select * from    
( 
    select * from #tempB
    except
    select * from #tempA
) a;

--select * from #tempA
--select * from #tempB

drop table #tempTA, #tempTB, #tempCA, #tempCB, #tempA, #tempB