（5.3.4）数据库迁移——数据对比（结构、数据类型）

关键词：数据对比，数据结构对比，数据类型对比

使用场景：批量对比

 

【1】数据结构对比（实例内）

单实例不建议使用，建议使用 red Gate 工具集中的 SQL Compare

但如果没有装软件，可以使用脚本实现

 

/*
    作用，
        1.对比2个库字段类型是否相同
        2.对比2个库字段数量是否相同

*/
--（1）主从表字段差异与表结构差异
use db_del
go
    if object_id('temp_logs1') is not null
        drop table temp_logs1
    if object_id('temp_logs2') is not null
        drop table temp_logs2
    if object_id('temp_tank1') is not null
        drop table temp_tank1
    if object_id('temp_tank2') is not null
        drop table temp_tank2

use db_logs

    select object_name(object_id) as tab_name,t2.name as column_type,
    t1.object_id,t1.name ,t1.system_type_id,t1.user_type_id,t1.max_length into db_del..temp_logs1
    from sys.columns t1 join sys.types t2 on t1.user_type_id=t2.user_type_id and t1.user_type_id=t2.user_type_id

use logs2

    select object_name(object_id) as tab_name,t2.name as column_type,
    t1.object_id,t1.name,t1.system_type_id,t1.user_type_id,t1.max_length into db_del..temp_logs2
    from sys.columns t1 join sys.types t2 on t1.user_type_id=t2.user_type_id and t1.user_type_id=t2.user_type_id

use db_tank

    select object_name(object_id) as tab_name,t2.name as column_type,
    t1.object_id,t1.name,t1.system_type_id,t1.user_type_id,t1.max_length into db_del..temp_tank1
    from sys.columns t1 join sys.types t2 on t1.user_type_id=t2.user_type_id and t1.user_type_id=t2.user_type_id

use tank2

    select object_name(object_id) as tab_name,t2.name as column_type,
    t1.object_id,t1.name,t1.system_type_id,t1.user_type_id,t1.max_length into db_del..temp_tank2
    from sys.columns t1 join sys.types t2 on t1.user_type_id=t2.user_type_id and t1.user_type_id=t2.user_type_id

--select logs
    select t1.*,t2.* from db_del..temp_logs1 t1  full join db_del..temp_logs2 t2
    on t1.tab_name=t2.tab_name and t1.name=t2.name
    where 

    ( 
            t2.tab_name is null or 
            t1.tab_name is null or
            t2.system_type_id!=t1.system_type_id or
            t2.user_type_id!= t1.user_type_id
    )
--select tank
    select t1.*,t2.* from db_del..temp_tank1 t1  full join db_del..temp_tank2 t2
    on t1.tab_name=t2.tab_name and t1.name=t2.name
    where 

    ( 
            t2.tab_name is null or 
            t1.tab_name is null or
            t2.system_type_id!=t1.system_type_id or
            t2.user_type_id!= t1.user_type_id
    )




--（2）获取表字段差异与表结构差异 db_tank与db_Del   可以理解成与历史库或历史表的对比    （2）这段可以忽略，用（1） 即可

--prepare
    use db_del
    go
    if object_id('temp_logs1') is not null
        drop table temp_logs1
    if object_id('temp_logs2') is not null
        drop table temp_logs2

    use db_tank

    select object_name(object_id) as tab_name,t2.name as column_type,
    t1.object_id,t1.name ,t1.system_type_id,t1.user_type_id,t1.max_length into db_del..temp_logs1
    from sys.columns t1 join sys.types t2 on t1.user_type_id=t2.user_type_id and t1.user_type_id=t2.user_type_id

    use db_del

    select object_name(object_id) as tab_name,t2.name as column_type,
    t1.object_id,t1.name,t1.system_type_id,t1.user_type_id,t1.max_length into db_del..temp_logs2
    from sys.columns t1 join sys.types t2 on t1.user_type_id=t2.user_type_id and t1.user_type_id=t2.user_type_id


--select tank库为主 与del与其同步比较
    select t1.*,t2.* from db_del..temp_logs1 t1  full join db_del..temp_logs2 t2
    on t1.tab_name=right(t2.tab_name,len(t2.tab_name)-4) and t1.name=t2.name
    where 
    t1.tab_name in  (
        select t1.tab_name from db_del..temp_logs1 t1 join 
        db_del..temp_logs2 t2 on t1.tab_name=right(t2.tab_name,len(t2.tab_name)-4)
    ) 
    and ( 
            t2.tab_name is null or 
            t1.tab_name is null or
            t2.system_type_id!=t1.system_type_id or
            t2.user_type_id!= t1.user_type_id
    )


--select del库为主，tank库与其同步比较


select t1.*,t2.* from db_del..temp_logs1 t1 full join db_del..temp_logs2 t2
on t1.tab_name=right(t2.tab_name,len(t2.tab_name)-4) and t1.name=t2.name
where 
t2.tab_name in (
　　select t2.tab_name from db_del..temp_logs1 t1 join 
　　db_del..temp_logs2 t2 on t1.tab_name=right(t2.tab_name,len(t2.tab_name)-4)
) 
and ( 
　　t2.tab_name is null or 
　　t1.tab_name is null or
　　t2.system_type_id!=t1.system_type_id or
　　t2.user_type_id!= t1.user_type_id
)

 

 

 

【2】跨DB对比，该段参考引用自：https://www.cnblogs.com/zhang502219048/p/11028767.html

单实例不建议使用，建议使用 red Gate 工具集中的 SQL Compare

但如果没有装软件，可以使用脚本实现

　　

 

 

-- sysobjects插入临时表
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插入临时表
select * into #tempCA from DB_V1.dbo.syscolumns 
select * into #tempCB from [localhost].DB_V2.dbo.syscolumns

-- 第一个数据库表和字段 
select b.TableName as 表名, a.name as 字段名, a.length as 长度, c.name as 类型
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 
-- 第二个数据库表和字段 
select b.TableName as 表名, a.name as 字段名, a.length as 长度, c.name as 类型
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