Navigation

	Home Page
	Index
	All Pages

Contributor Links

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

Other Wiki Sections

Software

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

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
        )