Navigation

	Home Page
	Index
	All Pages

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

Schema Differences Between Databases - SQL Server

SQL Server 2000¶

SELECT DISTINCT
    TABLE_NAME = TBL_DEV.NAME, 
    COLUMN_NAME = COL_DEV.NAME,
    STATUS = CASE 

            -- New Columns ------------------------------------------------------------------------
            WHEN NOT EXISTS (SELECT 2 
                                FROM 
                                    FEDRPL.DBO.SYSOBJECTS TBL_PROD 
                                    INNER JOIN FEDRPL.DBO.SYSCOLUMNS COL_PROD
                                        ON TBL_PROD.ID = COL_PROD.ID
                                WHERE 1=1 
                                AND TBL_PROD.TYPE = 'U' 
                                AND TBL_PROD.NAME = TBL_DEV.NAME
                                AND COL_PROD.NAME = COL_DEV.NAME
                                )
                THEN 'New Column'

            -- Changed Column ---------------------------------------------------------------------
            WHEN NOT EXISTS (SELECT 2 
                                FROM 
                                    FEDRPL.DBO.SYSOBJECTS TBL_PROD
                                    INNER JOIN FEDRPL.DBO.SYSCOLUMNS COL_PROD
                                        ON TBL_PROD.ID = COL_PROD.ID
                                WHERE 1=1
                                    AND TBL_PROD.NAME = TBL_DEV.NAME
                                    AND COL_PROD.NAME = COL_DEV.NAME
                                    AND COL_PROD.XTYPE = COL_DEV.XTYPE
                                    AND COL_PROD.LENGTH = COL_DEV.LENGTH
                                    AND COL_PROD.PREC = COL_DEV.PREC
                                    AND COALESCE(COL_PROD.SCALE,-1) = COALESCE(COL_DEV.SCALE,-1)
                                    AND COL_PROD.ISNULLABLE = COL_DEV.ISNULLABLE
                                    AND COL_PROD.STATUS & 128 = COL_DEV.STATUS & 128
                                )
                THEN 'Changed Column Type, Size, Prec, Scale, Nullability, or Identity'

            ELSE ''
            END,

    DATA_TYPE = TYP_DEV.NAME,
    DATA_SIZE = COL_DEV.LENGTH,
    DATA_PREC = COL_DEV.PREC,
    DATA_SCALE = coalesce(COL_DEV.SCALE,-1) ,
    COL_DEV.ISNULLABLE, 
    IDENTITY_COL = CASE WHEN COL_DEV.STATUS & 128 > 0 THEN 1 ELSE 0 END,    
    PK_FLAG = CASE WHEN COL_DEV.COLID IN 
            (SELECT K.COLID FROM 
                FEDRPL_DEV.DBO.SYSINDEXES I 
                INNER JOIN FEDRPL_DEV.DBO.SYSINDEXKEYS K ON I.INDID = K.INDID AND I.ID = K.ID
                INNER JOIN FEDRPL_DEV.DBO.SYSOBJECTS O2 ON I.ID = O2.ID 
                INNER JOIN FEDRPL_DEV.DBO.SYSCOLUMNS C2 ON K.ID = C2.ID AND K.COLID = C2.COLID
            WHERE (I.STATUS & 0x800) = 0x800
                AND O2.ID = TBL_DEV.ID) THEN 1 ELSE 0 END

FROM 

    FEDRPL_DEV.DBO.SYSOBJECTS TBL_DEV

    INNER JOIN FEDRPL_DEV.DBO.SYSCOLUMNS COL_DEV
        ON TBL_DEV.ID = COL_DEV.ID

    INNER JOIN FEDRPL_DEV.DBO.SYSTYPES TYP_DEV
        ON TYP_DEV.XTYPE = COL_DEV.XTYPE

WHERE 1=1
    AND TBL_DEV.TYPE = 'U'
    AND TYP_DEV.NAME NOT IN ('SYSNAME')
    AND TBL_DEV.NAME NOT IN ('dtproperties')
    AND EXISTS (SELECT 2 FROM FEDRPL.DBO.SYSOBJECTS TBL_PROD WHERE TYPE = 'U' AND NAME = TBL_DEV.NAME)
    AND NOT EXISTS (SELECT 2 
                FROM 
                    FEDRPL.DBO.SYSOBJECTS TBL_PROD
                    INNER JOIN FEDRPL.DBO.SYSCOLUMNS COL_PROD
                        ON TBL_PROD.ID = COL_PROD.ID
                WHERE 1=1
                    AND TBL_PROD.NAME = TBL_DEV.NAME
                    AND COL_PROD.NAME = COL_DEV.NAME
                    AND COL_PROD.XTYPE = COL_DEV.XTYPE
                    AND COL_PROD.LENGTH = COL_DEV.LENGTH
                    AND COL_PROD.PREC = COL_DEV.PREC
                    AND COALESCE(COL_PROD.SCALE,-1) = COALESCE(COL_DEV.SCALE,-1)
                    AND COL_PROD.ISNULLABLE = COL_DEV.ISNULLABLE
                    AND COL_PROD.STATUS & 128 = COL_DEV.STATUS & 128
                )
ORDER BY TBL_DEV.NAME--, COL_DEV.COLORDER

SQL Server 2005/2008¶

select distinct
     ObjectName     = '[' + a.SchemaName + '].[' + a.TableName + ']'
    ,Diff           = 'Table not deployed'
from
    AAA_Dev.dbo.DbLayout a
where 1=1
    and a.TableName not in (select TableName from AAA.dbo.DbLayout)

union
select 
     ObjectName  = '[' + a.SchemaName + '].[' + a.TableName + '].[' + a.ColumnName + ']'
    ,Diff           = case 
                        when b.SchemaName is null then 'Column not deployed'
                        else 'Column changed: ' 
                            + case when a.DataType <> b.DataType then 'DataType;' else '' end
                            + case when a.IsNullable <> b.IsNullable then 'IsNullable;' else '' end
                            + case when a.IsIdentity <> b.IsIdentity then 'IsIdentity;' else '' end
                            + case when a.IdentityDesc <> b.IdentityDesc then 'IdentityDesc;' else '' end
                            + case when a.IsPkey <> b.IsPkey then 'IsPkey;' else '' end
                        end

from
    AAA_Dev.dbo.DbLayout a
    left join AAA.dbo.DbLayout b
        on a.SchemaName = b.SchemaName
        and a.TableName = b.TableName
        and a.ColumnName = b.ColumnName
where 1=1
    and a.TableName not like '%$%'
    and a.TableName in (select TableName from AAA.dbo.DbLayout)
    and (
        1=2
        or b.SchemaName is null        
        or a.DataType <> b.DataType
        or a.IsNullable <> b.IsNullable
        or a.IsIdentity <> b.IsIdentity
        or a.IdentityDesc <> b.IdentityDesc
        or a.IsPkey <> b.IsPkey
        )

with SourceDbLayout as (
    select distinct

         DatabaseName   = db_name()
        ,SchemaName     = s.name
        ,TableName      = o.name 
        ,ColumnName     = c.name

        ,DataType       = case 
                            when t.name in ('char','text','varchar')

                                then t.name + '(' + convert(varchar,c.max_length) + ')'

                            when t.name in ('nchar','ntext','nvarchar')

                                then t.name + '(' + convert(varchar,c.max_length/2) + ')'

                            when t.name in ('decimal','numeric','real')

                                then t.name + '(' + convert(varchar,c.precision) + ',' + 
                                                    convert(varchar,c.scale) + ')'

                            else
     
                                t.name 

                            end

        ,IsNullable     = c.is_nullable 
        ,IsIdentity     = c.is_identity
        
        ,IdentityDesc   = case when c.is_identity = 0 then '' else '(' 
                            + convert(varchar(50), IDENT_SEED(o.name)) 
                            + ', ' 
                            + convert(varchar(50), IDENT_INCR(o.name))
                            + ')' 
                            end

        ,IsPkey         = case when c.column_id in 
                            (select k.colid from 
                                SourceDatabaseName.sys.sysindexes i 
                                inner join SourceDatabaseName.sys.sysindexkeys k on i.indid = k.indid and i.id = k.id
                                inner join SourceDatabaseName.sys.sysobjects o2 on i.id = o2.id 
                                inner join SourceDatabaseName.sys.syscolumns c2 on k.id = c2.id and k.colid = c2.colid
                            where (i.status & 0x800) = 0x800
                                and o2.id = o.object_id) then 1 else 0 end

        ,ColumnOrder    = c.column_id

        ,DefaultValue   = coalesce((select definition 
                                    from SourceDatabaseName.sys.default_constraints 
                                    where object_id = c.default_object_id), '')

        ,ColumnDesc     = coalesce((
                            select value 
                            from fn_listextendedproperty(NULL, 'schema', s.name, 
                                    'table', o.name, 'column', c.name) 
                            where name='MS_Description'
                            ), '')

    from 
        SourceDatabaseName.sys.objects o

        inner join SourceDatabaseName.sys.columns c 
            on o.object_id = c.object_id

        inner join SourceDatabaseName.sys.types t 
            on t.system_type_id = c.system_type_id

        inner join SourceDatabaseName.sys.schemas s
            on o.schema_id = s.schema_id

    where 1=1
        and o.type in ('u','v')
        and t.name not in ('sysname')
        and o.name not in ('dtproperties')
    )
/*===============================================================================================*/
,TargetDbLayout as (
    select distinct

         DatabaseName   = db_name()
        ,SchemaName     = s.name
        ,TableName      = o.name 
        ,ColumnName     = c.name

        ,DataType       = case 
                            when t.name in ('char','text','varchar')

                                then t.name + '(' + convert(varchar,c.max_length) + ')'

                            when t.name in ('nchar','ntext','nvarchar')

                                then t.name + '(' + convert(varchar,c.max_length/2) + ')'

                            when t.name in ('decimal','numeric','real')

                                then t.name + '(' + convert(varchar,c.precision) + ',' + 
                                                    convert(varchar,c.scale) + ')'

                            else
     
                                t.name 

                            end

        ,IsNullable     = c.is_nullable 
        ,IsIdentity     = c.is_identity
        
        ,IdentityDesc   = case when c.is_identity = 0 then '' else '(' 
                            + convert(varchar(50), IDENT_SEED(o.name)) 
                            + ', ' 
                            + convert(varchar(50), IDENT_INCR(o.name))
                            + ')' 
                            end

        ,IsPkey         = case when c.column_id in 
                            (select k.colid from 
                                TargetDatabaseName.sys.sysindexes i 
                                inner join TargetDatabaseName.sys.sysindexkeys k on i.indid = k.indid and i.id = k.id
                                inner join TargetDatabaseName.sys.sysobjects o2 on i.id = o2.id 
                                inner join TargetDatabaseName.sys.syscolumns c2 on k.id = c2.id and k.colid = c2.colid
                            where (i.status & 0x800) = 0x800
                                and o2.id = o.object_id) then 1 else 0 end

        ,ColumnOrder    = c.column_id

        ,DefaultValue   = coalesce((select definition 
                                    from TargetDatabaseName.sys.default_constraints 
                                    where object_id = c.default_object_id), '')

        ,ColumnDesc     = coalesce((
                            select value 
                            from fn_listextendedproperty(NULL, 'schema', s.name, 
                                    'table', o.name, 'column', c.name) 
                            where name='MS_Description'
                            ), '')

    from 
        TargetDatabaseName.sys.objects o

        inner join TargetDatabaseName.sys.columns c 
            on o.object_id = c.object_id

        inner join TargetDatabaseName.sys.types t 
            on t.system_type_id = c.system_type_id

        inner join TargetDatabaseName.sys.schemas s
            on o.schema_id = s.schema_id

    where 1=1
        and o.type in ('u','v')
        and t.name not in ('sysname')
        and o.name not in ('dtproperties')
    )
/*===============================================================================================*/
select distinct
     ObjectName     = '[' + a.SchemaName + '].[' + a.TableName + ']'
    ,Diff           = 'Table not deployed'
from
    SourceDbLayout a
where 1=1
    and a.TableName not in (select TableName from TargetDbLayout)

union
select 
     ObjectName  = '[' + a.SchemaName + '].[' + a.TableName + '].[' + a.ColumnName + ']'
    ,Diff           = case 
                        when b.SchemaName is null then 'Column not deployed'
                        else 'Column changed: ' 
                            + case when a.DataType <> b.DataType then 'DataType;' else '' end
                            + case when a.IsNullable <> b.IsNullable then 'IsNullable;' else '' end
                            + case when a.IsIdentity <> b.IsIdentity then 'IsIdentity;' else '' end
                            + case when a.IdentityDesc <> b.IdentityDesc then 'IdentityDesc;' else '' end
                            + case when a.IsPkey <> b.IsPkey then 'IsPkey;' else '' end
                        end

from
    SourceDbLayout a
    left join TargetDbLayout b
        on a.SchemaName = b.SchemaName
        and a.TableName = b.TableName
        and a.ColumnName = b.ColumnName
where 1=1
    and a.TableName not like '%$%'
    and a.TableName in (select TableName from TargetDbLayout)
    and (
        1=2
        or b.SchemaName is null        
        or a.DataType <> b.DataType
        or a.IsNullable <> b.IsNullable
        or a.IsIdentity <> b.IsIdentity
        or a.IdentityDesc <> b.IdentityDesc
        or a.IsPkey <> b.IsPkey
        )