Page History: Schema Differences Between Databases - SQL Server

Compare Page Revisions

3 2 1 0 Current 3 2 1 0

« Older Revision - Back to Page History - Newer Revision »

Page Revision: Wed, Jan 28, 2009, 7:27 PM

The following SQL will reveal the schema differences between two databases.

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