sql server 怎么对比数据库差异 sqlserver对比两个数据库

在有些时候， 需要知道测试站和正式站的数据库有那些不同， 如果单纯用肉眼去检查，那几乎是不可能完成的事情。这个时候我们就需要借助一些系统内置的表来进行关联查询了。

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROC [dbo].[p_comparestructure]
    @main VARCHAR(250) ,--要比较的主数据库名(正式站)
    @sub VARCHAR(250) --要比较的从数据库名(测试站) 
AS --主数据库 数据插入表
    CREATE TABLE #FF_MainTable
        (
          F_Main_TableName VARCHAR(250) ,  --表名
          F_Main_FieldName VARCHAR(250) , --字段名
          F_Main_Colid INT , --序号
          F_Main_Status BIT , --标识
          F_Main_PK BIT , --主键
          F_Main_Type VARCHAR(250) , --类型
          F_Main_Length INT , --占用字节数
          F_Main_Prec INT , --长度
          F_Main_Scale INT , --小数位数
          F_Main_Isnullable BIT , --允许空
          F_Main_Text SQL_VARIANT , --默认值
          F_Main_Value SQL_VARIANT  --字段说明
        ); 

	--从数据库 数据插入表
    CREATE TABLE #FF_SubTable
        (
          F_SubTable_TableName VARCHAR(250) ,  --表名
          F_SubTable_FieldName VARCHAR(250) , --字段名
          F_SubTable_Colid INT , --序号
          F_SubTable_Status BIT , --标识
          F_SubTable_PK BIT , --主键
          F_SubTable_Type VARCHAR(250) , --类型
          F_SubTable_Length INT , --占用字节数
          F_SubTable_Prec INT , --长度
          F_SubTable_Scale INT , --小数位数
          F_SubTable_Isnullable BIT , --允许空
          F_SubTable_Text SQL_VARIANT , --默认值
          F_SubTable_Value SQL_VARIANT  --字段说明
        ); 
  
--得到主数据库的结构 
    EXEC('INSERT  INTO #FF_MainTable
    SELECT  F_Main_TableName = d.name ,
    F_Main_FieldName = a.name ,
    F_Main_Colid = a.colid ,
    F_Main_Status = CASE WHEN a.status = 0x80 THEN 1
    ELSE 0
    END ,
    F_Main_PK = CASE WHEN EXISTS ( SELECT  1
    FROM    '+@main+'..sysobjects
    WHERE   xtype = ''PK''
    AND parent_obj = a.id
    AND name IN (
    SELECT  name
    FROM    '+@main+'..sysindexes
    WHERE   indid IN (
    SELECT
    indid
    FROM  '+@main+'..sysindexkeys
    WHERE id = a.id
    AND colid = a.colid ) ) )
    THEN 1
    ELSE 0
    END ,
    F_Main_Type = b.name ,
    F_Main_Length = a.length ,
    F_Main_Prec = a.prec ,
    F_Main_Scale = a.scale ,
    F_SubTable_Isnullable = a.isnullable ,
    F_Main_Text = ISNULL(e.text, '''') ,
    F_SubTable_Value = ISNULL(g.[value], '''')
    FROM    '+@main+'..syscolumns a
    LEFT JOIN '+@main+'..systypes b ON a.xtype = b.xusertype
    INNER JOIN '+@main+'..sysobjects d ON a.id = d.id
    AND d.xtype = ''U''
    AND d.name <> ''dtproperties''
    LEFT JOIN '+@main+'..syscomments e ON a.cdefault = e.id
    LEFT JOIN '+@main+'.sys.extended_properties g ON a.id = g.major_id
    AND a.colid = g.minor_id
    ORDER BY a.id ,
    a.colorder'); 
  
--得到从数据库的结构 
    EXEC('INSERT  INTO #FF_SubTable
    SELECT  F_SubTable_TableName = d.name ,
    F_SubTable_FieldName = a.name ,
    F_SubTable_Colid = a.colid ,
    F_SubTable_Status = CASE WHEN a.status = 0x80 THEN 1
    ELSE 0
    END ,
    F_SubTable_PK = CASE WHEN EXISTS ( SELECT  1
    FROM    '+@sub+'..sysobjects
    WHERE   xtype = ''PK''
    AND parent_obj = a.id
    AND name IN (
    SELECT  name
    FROM    '+@sub+'..sysindexes
    WHERE   indid IN (
    SELECT
    indid
    FROM  '+@sub+'..sysindexkeys
    WHERE id = a.id
    AND colid = a.colid ) ) )
    THEN 1
    ELSE 0
    END ,
    F_SubTable_Type = b.name ,
    F_SubTable_Length = a.length ,
    F_SubTable_Prec = a.prec ,
    F_SubTable_Scale = a.scale ,
    F_SubTable_Isnullable = a.isnullable ,
    F_SubTable_Text = ISNULL(e.text, '''') ,
    F_SubTable_Value = ISNULL(g.[value], '''')
    FROM    '+@sub+'..syscolumns a
    LEFT JOIN '+@sub+'..systypes b ON a.xtype = b.xusertype
    INNER JOIN '+@sub+'..sysobjects d ON a.id = d.id
    AND d.xtype = ''U''
    AND d.name <> ''dtproperties''
    LEFT JOIN '+@sub+'..syscomments e ON a.cdefault = e.id
    LEFT JOIN '+@sub+'.sys.extended_properties g ON a.id = g.major_id
    AND a.colid = g.minor_id
    ORDER BY a.id ,
    a.colorder'); 



    SELECT  比较结果 = CASE WHEN a.F_Main_TableName IS NULL
                             AND b.F_SubTable_Colid = 1 THEN '主数据库缺少表'
                        WHEN a.F_Main_TableName IS NULL
                             AND NOT EXISTS ( SELECT    1
                                              FROM      #FF_MainTable
                                              WHERE     F_Main_TableName = b.F_SubTable_TableName )
                        THEN '主数据库缺少表明细'
                        WHEN b.F_SubTable_TableName IS NULL
                             AND a.F_Main_Colid = 1 THEN '从数据库缺少表'
                        WHEN b.F_SubTable_TableName IS NULL
                             AND NOT EXISTS ( SELECT    1
                                              FROM      #FF_SubTable
                                              WHERE     F_SubTable_TableName = a.F_Main_TableName )
                        THEN '从数据库缺少表明细'
                        WHEN a.F_Main_FieldName IS NULL
                             AND EXISTS ( SELECT    1
                                          FROM      #FF_MainTable
                                          WHERE     F_Main_TableName = b.F_SubTable_TableName )
                        THEN '主数据库表' + b.F_SubTable_TableName + '缺少字段'
                        WHEN b.F_SubTable_FieldName IS NULL
                             AND EXISTS ( SELECT    1
                                          FROM      #FF_SubTable
                                          WHERE     F_SubTable_TableName = a.F_Main_TableName )
                        THEN '从数据库表' + a.F_Main_TableName + '缺少字段'
                        WHEN a.F_Main_Status <> b.F_SubTable_Status
                        THEN '标识不同'
                        WHEN a.F_Main_PK <> b.F_SubTable_PK THEN '主键设置不同'
                        WHEN a.F_Main_Type <> b.F_SubTable_Type THEN '字段类型不同'
                        WHEN a.F_Main_Length <> b.F_SubTable_Length
                        THEN '占用字节数'
                        WHEN a.F_Main_Prec <> b.F_SubTable_Prec THEN '长度不同'
                        WHEN a.F_Main_Scale <> b.F_SubTable_Scale
                        THEN '小数位数不同'
                        WHEN a.F_Main_Isnullable <> b.F_SubTable_Isnullable
                        THEN '是否允许空不同'
                        WHEN a.F_Main_Text <> b.F_SubTable_Text THEN '默认值不同'
                        WHEN a.F_Main_Value <> b.F_SubTable_Value
                        THEN '字段说明不同'
                        ELSE ''
                   END ,
            *
    FROM    #FF_MainTable a
            FULL JOIN #FF_SubTable b ON a.F_Main_TableName = b.F_SubTable_TableName
                                        AND a.F_Main_FieldName = b.F_SubTable_FieldName
    WHERE   a.F_Main_TableName IS NULL
            OR a.F_Main_FieldName IS NULL
            OR b.F_SubTable_TableName IS NULL
            OR b.F_SubTable_FieldName IS NULL
            OR a.F_Main_Status <> b.F_SubTable_Status
            OR a.F_Main_PK <> b.F_SubTable_PK
            OR a.F_Main_Type <> b.F_SubTable_Type
            OR a.F_Main_Length <> b.F_SubTable_Length
            OR a.F_Main_Prec <> b.F_SubTable_Prec
            OR a.F_Main_Scale <> b.F_SubTable_Scale
            OR a.F_Main_Isnullable <> b.F_SubTable_Isnullable
            OR a.F_Main_Text <> b.F_SubTable_Text
            OR a.F_Main_Value <> b.F_SubTable_Value
    UNION
    SELECT  比较结果 = CASE WHEN a.F_Main_PK <> b.F_SubTable_PK THEN '主键设置不同'
                        WHEN a.F_Main_Type <> b.F_SubTable_Type THEN '字段类型不同'
                        WHEN a.F_Main_Length <> b.F_SubTable_Length
                        THEN '占用字节数'
                        WHEN a.F_Main_Prec <> b.F_SubTable_Prec THEN '长度不同'
                        WHEN a.F_Main_Scale <> b.F_SubTable_Scale
                        THEN '小数位数不同'
                        WHEN a.F_Main_Isnullable <> b.F_SubTable_Isnullable
                        THEN '是否允许空不同'
                        WHEN a.F_Main_Text <> b.F_SubTable_Text THEN '默认值不同'
                        WHEN a.F_Main_Value <> b.F_SubTable_Value
                        THEN '字段说明不同'
                        WHEN a.F_Main_Status <> b.F_SubTable_Status
                        THEN '标识不同'
                        ELSE ''
                   END ,
            *
    FROM    #FF_MainTable a
            FULL JOIN #FF_SubTable b ON a.F_Main_TableName = b.F_SubTable_TableName
                                        AND a.F_Main_FieldName = b.F_SubTable_FieldName
    WHERE   a.F_Main_Status <> b.F_SubTable_Status
            OR a.F_Main_PK <> b.F_SubTable_PK
            OR a.F_Main_Type <> b.F_SubTable_Type
            OR a.F_Main_Length <> b.F_SubTable_Length
            OR a.F_Main_Prec <> b.F_SubTable_Prec
            OR a.F_Main_Scale <> b.F_SubTable_Scale
            OR a.F_Main_Isnullable <> b.F_SubTable_Isnullable
            OR a.F_Main_Text <> b.F_SubTable_Text
            OR a.F_Main_Value <> b.F_SubTable_Value
    UNION
    SELECT  比较结果 = CASE WHEN a.F_Main_Type <> b.F_SubTable_Type THEN '字段类型不同'
                        WHEN a.F_Main_Length <> b.F_SubTable_Length
                        THEN '占用字节数'
                        WHEN a.F_Main_Prec <> b.F_SubTable_Prec THEN '长度不同'
                        WHEN a.F_Main_Scale <> b.F_SubTable_Scale
                        THEN '小数位数不同'
                        WHEN a.F_Main_Isnullable <> b.F_SubTable_Isnullable
                        THEN '是否允许空不同'
                        WHEN a.F_Main_Text <> b.F_SubTable_Text THEN '默认值不同'
                        WHEN a.F_Main_Value <> b.F_SubTable_Value
                        THEN '字段说明不同'
                        WHEN a.F_Main_Status <> b.F_SubTable_Status
                        THEN '标识不同'
                        WHEN a.F_Main_PK <> b.F_SubTable_PK THEN '主键设置不同'
                        ELSE ''
                   END ,
            *
    FROM    #FF_MainTable a
            FULL JOIN #FF_SubTable b ON a.F_Main_TableName = b.F_SubTable_TableName
                                        AND a.F_Main_FieldName = b.F_SubTable_FieldName
    WHERE   a.F_Main_Status <> b.F_SubTable_Status
            OR a.F_Main_PK <> b.F_SubTable_PK
            OR a.F_Main_Type <> b.F_SubTable_Type
            OR a.F_Main_Length <> b.F_SubTable_Length
            OR a.F_Main_Prec <> b.F_SubTable_Prec
            OR a.F_Main_Scale <> b.F_SubTable_Scale
            OR a.F_Main_Isnullable <> b.F_SubTable_Isnullable
            OR a.F_Main_Text <> b.F_SubTable_Text
            OR a.F_Main_Value <> b.F_SubTable_Value
    UNION
    SELECT  比较结果 = CASE WHEN a.F_Main_Length <> b.F_SubTable_Length
                        THEN '占用字节数'
                        WHEN a.F_Main_Prec <> b.F_SubTable_Prec THEN '长度不同'
                        WHEN a.F_Main_Scale <> b.F_SubTable_Scale
                        THEN '小数位数不同'
                        WHEN a.F_Main_Isnullable <> b.F_SubTable_Isnullable
                        THEN '是否允许空不同'
                        WHEN a.F_Main_Text <> b.F_SubTable_Text THEN '默认值不同'
                        WHEN a.F_Main_Value <> b.F_SubTable_Value
                        THEN '字段说明不同'
                        WHEN a.F_Main_Status <> b.F_SubTable_Status
                        THEN '标识不同'
                        WHEN a.F_Main_PK <> b.F_SubTable_PK THEN '主键设置不同'
                        WHEN a.F_Main_Type <> b.F_SubTable_Type THEN '字段类型不同'
                        ELSE ''
                   END ,
            *
    FROM    #FF_MainTable a
            FULL JOIN #FF_SubTable b ON a.F_Main_TableName = b.F_SubTable_TableName
                                        AND a.F_Main_FieldName = b.F_SubTable_FieldName
    WHERE   a.F_Main_Status <> b.F_SubTable_Status
            OR a.F_Main_PK <> b.F_SubTable_PK
            OR a.F_Main_Type <> b.F_SubTable_Type
            OR a.F_Main_Length <> b.F_SubTable_Length
            OR a.F_Main_Prec <> b.F_SubTable_Prec
            OR a.F_Main_Scale <> b.F_SubTable_Scale
            OR a.F_Main_Isnullable <> b.F_SubTable_Isnullable
            OR a.F_Main_Text <> b.F_SubTable_Text
            OR a.F_Main_Value <> b.F_SubTable_Value
    UNION
    SELECT  比较结果 = CASE WHEN a.F_Main_Prec <> b.F_SubTable_Prec THEN '长度不同'
                        WHEN a.F_Main_Scale <> b.F_SubTable_Scale
                        THEN '小数位数不同'
                        WHEN a.F_Main_Isnullable <> b.F_SubTable_Isnullable
                        THEN '是否允许空不同'
                        WHEN a.F_Main_Text <> b.F_SubTable_Text THEN '默认值不同'
                        WHEN a.F_Main_Value <> b.F_SubTable_Value
                        THEN '字段说明不同'
                        WHEN a.F_Main_Status <> b.F_SubTable_Status
                        THEN '标识不同'
                        WHEN a.F_Main_PK <> b.F_SubTable_PK THEN '主键设置不同'
                        WHEN a.F_Main_Type <> b.F_SubTable_Type THEN '字段类型不同'
                        WHEN a.F_Main_Length <> b.F_SubTable_Length
                        THEN '占用字节数'
                        ELSE ''
                   END ,
            *
    FROM    #FF_MainTable a
            FULL JOIN #FF_SubTable b ON a.F_Main_TableName = b.F_SubTable_TableName
                                        AND a.F_Main_FieldName = b.F_SubTable_FieldName
    WHERE   a.F_Main_Status <> b.F_SubTable_Status
            OR a.F_Main_PK <> b.F_SubTable_PK
            OR a.F_Main_Type <> b.F_SubTable_Type
            OR a.F_Main_Length <> b.F_SubTable_Length
            OR a.F_Main_Prec <> b.F_SubTable_Prec
            OR a.F_Main_Scale <> b.F_SubTable_Scale
            OR a.F_Main_Isnullable <> b.F_SubTable_Isnullable
            OR a.F_Main_Text <> b.F_SubTable_Text
            OR a.F_Main_Value <> b.F_SubTable_Value
    UNION
    SELECT  比较结果 = CASE WHEN a.F_Main_Scale <> b.F_SubTable_Scale
                        THEN '小数位数不同'
                        WHEN a.F_Main_Isnullable <> b.F_SubTable_Isnullable
                        THEN '是否允许空不同'
                        WHEN a.F_Main_Text <> b.F_SubTable_Text THEN '默认值不同'
                        WHEN a.F_Main_Value <> b.F_SubTable_Value
                        THEN '字段说明不同'
                        WHEN a.F_Main_Status <> b.F_SubTable_Status
                        THEN '标识不同'
                        WHEN a.F_Main_PK <> b.F_SubTable_PK THEN '主键设置不同'
                        WHEN a.F_Main_Type <> b.F_SubTable_Type THEN '字段类型不同'
                        WHEN a.F_Main_Length <> b.F_SubTable_Length
                        THEN '占用字节数'
                        WHEN a.F_Main_Prec <> b.F_SubTable_Prec THEN '长度不同'
                        ELSE ''
                   END ,
            *
    FROM    #FF_MainTable a
            FULL JOIN #FF_SubTable b ON a.F_Main_TableName = b.F_SubTable_TableName
                                        AND a.F_Main_FieldName = b.F_SubTable_FieldName
    WHERE   a.F_Main_Status <> b.F_SubTable_Status
            OR a.F_Main_PK <> b.F_SubTable_PK
            OR a.F_Main_Type <> b.F_SubTable_Type
            OR a.F_Main_Length <> b.F_SubTable_Length
            OR a.F_Main_Prec <> b.F_SubTable_Prec
            OR a.F_Main_Scale <> b.F_SubTable_Scale
            OR a.F_Main_Isnullable <> b.F_SubTable_Isnullable
            OR a.F_Main_Text <> b.F_SubTable_Text
            OR a.F_Main_Value <> b.F_SubTable_Value
    UNION
    SELECT  比较结果 = CASE WHEN a.F_Main_Isnullable <> b.F_SubTable_Isnullable
                        THEN '是否允许空不同'
                        WHEN a.F_Main_Text <> b.F_SubTable_Text THEN '默认值不同'
                        WHEN a.F_Main_Value <> b.F_SubTable_Value
                        THEN '字段说明不同'
                        WHEN a.F_Main_Status <> b.F_SubTable_Status
                        THEN '标识不同'
                        WHEN a.F_Main_PK <> b.F_SubTable_PK THEN '主键设置不同'
                        WHEN a.F_Main_Type <> b.F_SubTable_Type THEN '字段类型不同'
                        WHEN a.F_Main_Length <> b.F_SubTable_Length
                        THEN '占用字节数'
                        WHEN a.F_Main_Prec <> b.F_SubTable_Prec THEN '长度不同'
                        WHEN a.F_Main_Scale <> b.F_SubTable_Scale
                        THEN '小数位数不同'
                        ELSE ''
                   END ,
            *
    FROM    #FF_MainTable a
            FULL JOIN #FF_SubTable b ON a.F_Main_TableName = b.F_SubTable_TableName
                                        AND a.F_Main_FieldName = b.F_SubTable_FieldName
    WHERE   a.F_Main_Status <> b.F_SubTable_Status
            OR a.F_Main_PK <> b.F_SubTable_PK
            OR a.F_Main_Type <> b.F_SubTable_Type
            OR a.F_Main_Length <> b.F_SubTable_Length
            OR a.F_Main_Prec <> b.F_SubTable_Prec
            OR a.F_Main_Scale <> b.F_SubTable_Scale
            OR a.F_Main_Isnullable <> b.F_SubTable_Isnullable
            OR a.F_Main_Text <> b.F_SubTable_Text
            OR a.F_Main_Value <> b.F_SubTable_Value
    UNION
    SELECT  比较结果 = CASE WHEN a.F_Main_Text <> b.F_SubTable_Text THEN '默认值不同'
                        WHEN a.F_Main_Value <> b.F_SubTable_Value
                        THEN '字段说明不同'
                        WHEN a.F_Main_Status <> b.F_SubTable_Status
                        THEN '标识不同'
                        WHEN a.F_Main_PK <> b.F_SubTable_PK THEN '主键设置不同'
                        WHEN a.F_Main_Type <> b.F_SubTable_Type THEN '字段类型不同'
                        WHEN a.F_Main_Length <> b.F_SubTable_Length
                        THEN '占用字节数'
                        WHEN a.F_Main_Prec <> b.F_SubTable_Prec THEN '长度不同'
                        WHEN a.F_Main_Scale <> b.F_SubTable_Scale
                        THEN '小数位数不同'
                        WHEN a.F_Main_Isnullable <> b.F_SubTable_Isnullable
                        THEN '是否允许空不同'
                        ELSE ''
                   END ,
            *
    FROM    #FF_MainTable a
            FULL JOIN #FF_SubTable b ON a.F_Main_TableName = b.F_SubTable_TableName
                                        AND a.F_Main_FieldName = b.F_SubTable_FieldName
    WHERE   a.F_Main_Status <> b.F_SubTable_Status
            OR a.F_Main_PK <> b.F_SubTable_PK
            OR a.F_Main_Type <> b.F_SubTable_Type
            OR a.F_Main_Length <> b.F_SubTable_Length
            OR a.F_Main_Prec <> b.F_SubTable_Prec
            OR a.F_Main_Scale <> b.F_SubTable_Scale
            OR a.F_Main_Isnullable <> b.F_SubTable_Isnullable
            OR a.F_Main_Text <> b.F_SubTable_Text
            OR a.F_Main_Value <> b.F_SubTable_Value
    UNION
    SELECT  比较结果 = CASE WHEN a.F_Main_Value <> b.F_SubTable_Value
                        THEN '字段说明不同'
                        WHEN a.F_Main_Status <> b.F_SubTable_Status
                        THEN '标识不同'
                        WHEN a.F_Main_PK <> b.F_SubTable_PK THEN '主键设置不同'
                        WHEN a.F_Main_Type <> b.F_SubTable_Type THEN '字段类型不同'
                        WHEN a.F_Main_Length <> b.F_SubTable_Length
                        THEN '占用字节数'
                        WHEN a.F_Main_Prec <> b.F_SubTable_Prec THEN '长度不同'
                        WHEN a.F_Main_Scale <> b.F_SubTable_Scale
                        THEN '小数位数不同'
                        WHEN a.F_Main_Isnullable <> b.F_SubTable_Isnullable
                        THEN '是否允许空不同'
                        WHEN a.F_Main_Text <> b.F_SubTable_Text THEN '默认值不同'
                        ELSE ''
                   END ,
            *
    FROM    #FF_MainTable a
            FULL JOIN #FF_SubTable b ON a.F_Main_TableName = b.F_SubTable_TableName
                                        AND a.F_Main_FieldName = b.F_SubTable_FieldName
    WHERE   a.F_Main_Status <> b.F_SubTable_Status
            OR a.F_Main_PK <> b.F_SubTable_PK
            OR a.F_Main_Type <> b.F_SubTable_Type
            OR a.F_Main_Length <> b.F_SubTable_Length
            OR a.F_Main_Prec <> b.F_SubTable_Prec
            OR a.F_Main_Scale <> b.F_SubTable_Scale
            OR a.F_Main_Isnullable <> b.F_SubTable_Isnullable
            OR a.F_Main_Text <> b.F_SubTable_Text
            OR a.F_Main_Value <> b.F_SubTable_Value;

执行：

EXEC dbo.p_comparestructure @main = 'Test', -- varchar(250)
    @sub = 'Test1' -- varchar(250)

查询结果：