Home > Link, Microsoft SQL Server, MSBI, Optimization, Query, Script, SQL Mentalist, SQL PraRup, SQL Query, SQL Server, Technology,, TSQL, Vishal Pawar > BI SQL # 239 : SQL Server DBA Scripts : C. Compare Database

BI SQL # 239 : SQL Server DBA Scripts : C. Compare Database

Hi Folks,

In this article we are going to cover 3 and last type of C. Compare Database.

In this post we are going to discuss following points:

  • Problem Statement of SQL Script:
  • Description of SQL Script:
  • SQL Script Output Column
  • Input Parameter of SQL Script
  • SQL Script Code
  • SQL Script Output Screenshot
  • User Level to execute

    Problem Statement of SQL Script:

    Compare database.

    Description of SQL Script:

    This SQL script will compare table columns, Index and foreign keys in two databases, I am planning to continue this further to add more and more options to this. This will show a result with missing objects and the objects with definition mismatch.

    SQL Script Output Column

    clip_image001clip_image002clip_image003

    Input Parameter of SQL Script

    @SourceDB SYSNAME, @TargetDb SYSNAME

    SQL Script Code

    CREATE PROC sp_CompareDb (
        @SourceDB SYSNAME
        ,@TargetDb SYSNAME
        )
    AS
    /* 
    DECLARE @SourceDB SYSNAME='DB1',  
                      @TargetDb SYSNAME='DB2' 
    */
    SET NOCOUNT ON
    SET ANSI_WARNINGS ON
    SET ANSI_NULLS ON
    
    DECLARE @sqlStr VARCHAR(8000)
    
    SET @SourceDB = Rtrim(Ltrim(@SourceDB))
    
    IF DB_ID(@SourceDB) IS NULL
    BEGIN
        PRINT 'Error: Unable to find the database ' + @SourceDB + 
            '!!!'
    
        RETURN
    END
    
    SET @TargetDb = Rtrim(Ltrim(@TargetDb))
    
    IF DB_ID(@SourceDB) IS NULL
    BEGIN
        PRINT 'Error: Unable to find the database ' + @TargetDb + 
            '!!!'
    
        RETURN
    END
    
    PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDb) + 25)
    PRINT 'Comparing databases ' + @SourceDB + ' and ' + @TargetDb
    PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDb) + 25)
    
    IF OBJECT_ID('TEMPDB..#TABLIST_SOURCE') IS NOT NULL
        DROP TABLE #TABLIST_SOURCE
    
    IF OBJECT_ID('TEMPDB..#TABLIST_TARGET') IS NOT NULL
        DROP TABLE #TABLIST_TARGET
    
    IF OBJECT_ID('TEMPDB..#IDXLIST_SOURCE') IS NOT NULL
        DROP TABLE #IDXLIST_SOURCE
    
    IF OBJECT_ID('TEMPDB..#IDXLIST_TARGET') IS NOT NULL
        DROP TABLE #IDXLIST_TARGET
    
    IF OBJECT_ID('TEMPDB..#FKLIST_SOURCE') IS NOT NULL
        DROP TABLE #FKLIST_SOURCE
    
    IF OBJECT_ID('TEMPDB..#FKLIST_TARGET') IS NOT NULL
        DROP TABLE #FKLIST_TARGET
    
    IF OBJECT_ID('TEMPDB..#TAB_RESULTS') IS NOT NULL
        DROP TABLE #TAB_RESULTS
    
    IF OBJECT_ID('TEMPDB..#IDX_RESULTS') IS NOT NULL
        DROP TABLE #IDX_RESULTS
    
    IF OBJECT_ID('TEMPDB..#FK_RESULTS') IS NOT NULL
        DROP TABLE #FK_RESULTS
    
    CREATE TABLE #TABLIST_SOURCE (
        ID INT IDENTITY(1, 1)
        ,DATABASENAME SYSNAME
        ,TABLENAME SYSNAME
        ,COLUMNNAME SYSNAME
        ,DATATYPE SYSNAME
        ,NULLABLE VARCHAR(15)
        )
    
    CREATE TABLE #TABLIST_TARGET (
        ID INT IDENTITY(1, 1)
        ,DATABASENAME SYSNAME
        ,TABLENAME SYSNAME
        ,COLUMNNAME SYSNAME
        ,DATATYPE SYSNAME
        ,NULLABLE VARCHAR(15)
        )
    
    CREATE TABLE #IDXLIST_SOURCE (
        ID INT IDENTITY(1, 1)
        ,DATABASENAME SYSNAME
        ,TABLE_NAME SYSNAME
        ,IDX_NAME SYSNAME
        ,IDX_TYPE VARCHAR(20)
        ,IS_PRIMARY_KEY VARCHAR(10)
        ,IS_UNIQUE VARCHAR(10)
        ,IDX_COLUMNS VARCHAR(1000)
        ,IDX_INCLUDED_COLUMNS VARCHAR(1000)
        );
    
    CREATE TABLE #IDXLIST_TARGET (
        ID INT IDENTITY(1, 1)
        ,DATABASENAME SYSNAME
        ,TABLE_NAME SYSNAME
        ,IDX_NAME SYSNAME
        ,IDX_TYPE VARCHAR(20)
        ,IS_PRIMARY_KEY VARCHAR(10)
        ,IS_UNIQUE VARCHAR(10)
        ,IDX_COLUMNS VARCHAR(1000)
        ,IDX_INCLUDED_COLUMNS VARCHAR(1000)
        );
    
    CREATE TABLE #FKLIST_SOURCE (
        ID INT IDENTITY(1, 1)
        ,DATABASENAME SYSNAME
        ,FK_NAME SYSNAME
        ,FK_TABLE SYSNAME
        ,FK_COLUMNS VARCHAR(1000)
        ,PK_TABLE SYSNAME
        ,PK_COLUMNS VARCHAR(1000)
        );
    
    CREATE TABLE #FKLIST_TARGET (
        ID INT IDENTITY(1, 1)
        ,DATABASENAME SYSNAME
        ,FK_NAME SYSNAME
        ,FK_TABLE SYSNAME
        ,FK_COLUMNS VARCHAR(1000)
        ,PK_TABLE SYSNAME
        ,PK_COLUMNS VARCHAR(1000)
        );
    
    CREATE TABLE #TAB_RESULTS (
        ID INT IDENTITY(1, 1)
        ,DATABASENAME SYSNAME
        ,TABLENAME SYSNAME
        ,COLUMNNAME SYSNAME
        ,DATATYPE SYSNAME
        ,NULLABLE VARCHAR(15)
        ,REASON VARCHAR(150)
        );
    
    CREATE TABLE #IDX_RESULTS (
        ID INT IDENTITY(1, 1)
        ,DATABASENAME SYSNAME
        ,TABLE_NAME SYSNAME
        ,IDX_NAME SYSNAME
        ,IDX_TYPE VARCHAR(20)
        ,IS_PRIMARY_KEY VARCHAR(10)
        ,IS_UNIQUE VARCHAR(10)
        ,IDX_COLUMNS VARCHAR(1000)
        ,IDX_INCLUDED_COLUMNS VARCHAR(1000)
        ,REASON VARCHAR(150)
        );
    
    CREATE TABLE #FK_RESULTS (
        ID INT IDENTITY(1, 1)
        ,DATABASENAME SYSNAME
        ,FK_NAME SYSNAME
        ,FK_TABLE SYSNAME
        ,FK_COLUMNS VARCHAR(1000)
        ,PK_TABLE SYSNAME
        ,PK_COLUMNS VARCHAR(1000)
        ,REASON VARCHAR(150)
        );
    
    PRINT 'Getting table and column list!';
    PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDb) + 25);
    
    BEGIN
        INSERT INTO #TABLIST_SOURCE (
            DATABASENAME
            ,TABLENAME
            ,COLUMNNAME
            ,DATATYPE
            ,NULLABLE
            )
        EXEC (
                'SELECT ''' + @SourceDB + 
                ''', T.TABLE_NAME TABLENAME,  
                 C.COLUMN_NAME COLUMNNAME, 
                 TY.name + case when TY.name IN 
    (''char'',''varchar'',''nvarchar'') THEN     
                    ''(''+CASE WHEN C.CHARACTER_MAXIMUM_LENGTH>0 THEN CAST
    (C.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) ELSE ''max''END+'')'' 
                    ELSE     
                        '''' 
                    END 
                    DATATYPE, 
                    CASE WHEN C.is_nullable=''NO'' THEN     
                        ''NOT NULL''  
                        ELSE 
                        ''NULL'' 
                    END NULLABLE 
                        FROM ' 
                + @SourceDB + 
                '.INFORMATION_SCHEMA.TABLES T  
                            INNER JOIN  ' 
                + @SourceDB + 
                '.INFORMATION_SCHEMA.COLUMNS C 
                                ON T.TABLE_NAME=C.TABLE_NAME 
                                and T.TABLE_CATALOG=C.TABLE_CATALOG 
                                and T.TABLE_SCHEMA=C.TABLE_SCHEMA 
                             INNER JOIN ' 
                + @SourceDB + 
                '.sys.types TY 
                            ON C.DATA_TYPE =TY.name         
                            ORDER BY TABLENAME, COLUMNNAME,C.ORDINAL_POSITION'
                );
    
        INSERT INTO #TABLIST_TARGET (
            DATABASENAME
            ,TABLENAME
            ,COLUMNNAME
            ,DATATYPE
            ,NULLABLE
            )
        EXEC (
                'SELECT ''' + @TargetDB + 
                ''', T.TABLE_NAME TABLENAME,  
                 C.COLUMN_NAME COLUMNNAME, 
                 TY.name + case when TY.name IN 
    (''char'',''varchar'',''nvarchar'') THEN     
                    ''(''+CASE WHEN C.CHARACTER_MAXIMUM_LENGTH>0 THEN CAST
    (C.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) ELSE ''max''END+'')'' 
                    ELSE     
                        '''' 
                    END 
                    DATATYPE, 
                    CASE WHEN C.is_nullable=''NO'' THEN     
                        ''NOT NULL''  
                        ELSE 
                        ''NULL'' 
                    END NULLABLE 
                        FROM ' 
                + @TargetDB + 
                '.INFORMATION_SCHEMA.TABLES T  
                            INNER JOIN  ' 
                + @TargetDB + 
                '.INFORMATION_SCHEMA.COLUMNS C 
                                ON T.TABLE_NAME=C.TABLE_NAME 
                                and T.TABLE_CATALOG=C.TABLE_CATALOG 
                                and T.TABLE_SCHEMA=C.TABLE_SCHEMA 
                             INNER JOIN ' 
                + @TargetDB + 
                '.sys.types TY 
                            ON C.DATA_TYPE =TY.name         
                            ORDER BY TABLENAME, COLUMNNAME,C.ORDINAL_POSITION'
                );
    
        PRINT 'Getting index list!';
        PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDb) + 25);
    
        INSERT INTO #IDXLIST_SOURCE (
            DATABASENAME
            ,TABLE_NAME
            ,IDX_NAME
            ,IDX_TYPE
            ,IS_PRIMARY_KEY
            ,IS_UNIQUE
            ,IDX_COLUMNS
            ,IDX_INCLUDED_COLUMNS
            )
        EXEC (
                'WITH CTE AS (  
               SELECT      ic.index_id + ic.object_id AS IndexId,t.name AS 
    TableName  
                           ,i.name AS IndexName 
                                                 ,case when 
    ic.is_included_column =0 then 
                                                        c.name end AS ColumnName 
                                                ,case when 
    ic.is_included_column =1 then 
                                                        c.name end AS 
    IncludedColumn 
                                                        ,i.type_desc  
                           ,i.is_primary_key,i.is_unique  
               FROM  ' 
                + @SourceDB + 
                '.sys.indexes i  
               INNER JOIN ' + 
                @SourceDB + 
                '.sys.index_columns ic  
                       ON  i.index_id    =   ic.index_id  
                       AND i.object_id   =   ic.object_id  
               INNER JOIN ' 
                + @SourceDB + 
                '.sys.columns c  
                       ON  ic.column_id  =   c.column_id  
                       AND i.object_id   =   c.object_id  
               INNER JOIN ' 
                + @SourceDB + 
                '.sys.tables t  
                       ON  i.object_id = t.object_id  
    )  
    SELECT ''' 
                + @SourceDB + 
                ''',c.TableName TABLE_NAME,c.IndexName INDEX_NAME,c.type_desc 
    INDEX_TYPE ,c.is_primary_key IS_PRIMARY_KEY,c.is_unique IS_UNIQUE 
           ,STUFF( ( SELECT '',''+ a.ColumnName FROM CTE a WHERE c.IndexId = 
    a.IndexId FOR XML PATH('''')),1 ,1, '''') AS COLUMNS 
           ,STUFF( ( SELECT '',''+ a.IncludedColumn FROM CTE a WHERE c.IndexId = 
    a.IndexId FOR XML PATH('''')),1 ,1, '''') AS INCLUDED_COLUMNS 
    FROM   CTE c  
    GROUP  BY c.IndexId,c.TableName,c.IndexName,
    c.type_desc,c.is_primary_key,c.is_unique  
    ORDER  BY c.TableName ASC,c.is_primary_key DESC; '
                );
    
        INSERT INTO #IDXLIST_TARGET (
            DATABASENAME
            ,TABLE_NAME
            ,IDX_NAME
            ,IDX_TYPE
            ,IS_PRIMARY_KEY
            ,IS_UNIQUE
            ,IDX_COLUMNS
            ,IDX_INCLUDED_COLUMNS
            )
        EXEC (
                'WITH CTE AS (  
               SELECT      ic.index_id + ic.object_id AS 
    IndexId,t.name AS TableName  
                           ,i.name AS IndexName 
                                                 ,case when 
    ic.is_included_column =0 then 
                                                        c.name end AS ColumnName 
                                                ,case when 
    ic.is_included_column =1 then 
                                                        c.name end AS 
    IncludedColumn 
                                                        ,i.type_desc  
                           ,i.is_primary_key,i.is_unique  
               FROM  ' 
                + @TargetDB + 
                '.sys.indexes i  
               INNER JOIN ' + 
                @TargetDB + 
                '.sys.index_columns ic  
                       ON  i.index_id    =   ic.index_id  
                       AND i.object_id   =   ic.object_id  
               INNER JOIN ' 
                + @TargetDB + 
                '.sys.columns c  
                       ON  ic.column_id  =   c.column_id  
                       AND i.object_id   =   c.object_id  
               INNER JOIN ' 
                + @TargetDB + 
                '.sys.tables t  
                       ON  i.object_id = t.object_id  
    )  
    SELECT ''' 
                + @TargetDB + 
                ''',c.TableName,c.IndexName,
    c.type_desc,c.is_primary_key,c.is_unique  
           ,STUFF( ( SELECT '',''+ a.ColumnName FROM CTE a WHERE 
    c.IndexId = a.IndexId FOR XML PATH('''')),1 ,1, '''') AS Columns  
           ,STUFF( ( SELECT '',''+ a.IncludedColumn FROM CTE a 
    WHERE c.IndexId = a.IndexId FOR XML PATH('''')),1 ,1, '''') AS IncludedColumns  
    FROM   CTE c  
    GROUP  BY c.IndexId,c.TableName,c.IndexName,
    c.type_desc,c.is_primary_key,c.is_unique  
    ORDER  BY c.TableName ASC,c.is_primary_key DESC; '
                );
    
        PRINT 'Getting foreign key list!';
        PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDb) + 25);
    
        INSERT INTO #FKLIST_SOURCE (
            DATABASENAME
            ,FK_NAME
            ,FK_TABLE
            ,FK_COLUMNS
            ,PK_TABLE
            ,PK_COLUMNS
            )
        EXEC (
                'With CTE 
                    AS 
                (select OBJECT_NAME(FK.parent_object_id,db_id(''' 
                + @TargetDB + 
                ''')) PK_TABLE,      
                            C1.name PK_COLUMN, 
                object_name(FK.referenced_object_id,db_id(''' 
                + @TargetDB + 
                '''))FK_TABLE, 
                C2.name FK_COLUMN, 
                FK.name     FK_NAME 
    from 
            ' 
                + @SourceDB + 
                '.sys.foreign_keys FK 
                inner join  
            ' 
                + @SourceDB + 
                '.sys.foreign_key_columns FKC 
                on FK.object_id=FKC.constraint_object_id 
                inner join  
            ' 
                + @SourceDB + 
                '.sys.columns C1  
                on FKC.parent_column_id=C1.column_id 
                and FKC.parent_object_id=C1.object_id 
                inner join  
            ' 
                + @SourceDB + 
                '.sys.columns C2 
                on FKC.referenced_column_id=C2.column_id 
                and FKC.referenced_object_id=C2.object_id                             
        ) 
    SELECT ''' 
                + @SourceDB + 
                ''',C.FK_NAME, 
                 C.FK_TABLE,             STUFF( ( SELECT '',''+ A.FK_COLUMN 
    FROM CTE a WHERE c.FK_NAME = a.FK_NAME and C.FK_TABLE=a.FK_TABLE FOR XML 
    PATH('''')),1 ,1, '''') AS FK_COLUMNS, 
                 C.PK_TABLE,                           
                 STUFF( ( SELECT '',''+ A.PK_Column FROM CTE a 
    WHERE c.FK_NAME = a.FK_NAME and C.PK_TABLE=a.PK_TABLE FOR XML 
    PATH('''')),1 ,1, '''') AS PK_COLUMNS  
    FROM CTE C 
    group by C.FK_NAME, 
                 C.FK_TABLE,              
                 C.PK_TABLE'
                )
    
        INSERT INTO #FKLIST_TARGET (
            DATABASENAME
            ,FK_NAME
            ,FK_TABLE
            ,FK_COLUMNS
            ,PK_TABLE
            ,PK_COLUMNS
            )
        EXEC (
                ' 
            With CTE 
    AS 
    (select OBJECT_NAME(FK.parent_object_id,db_id(''' 
                + @TargetDB + 
                ''')) PK_TABLE,      
                C1.name PK_COLUMN, 
                object_name(FK.referenced_object_id,db_id(''' 
                + @TargetDB + 
                '''))FK_TABLE, 
                C2.name FK_COLUMN, 
                FK.name     FK_NAME 
    from 
            ' 
                + @TargetDB + 
                '.sys.foreign_keys FK 
                inner join  
            ' 
                + @TargetDB + 
                '.sys.foreign_key_columns FKC 
                on FK.object_id=FKC.constraint_object_id 
                inner join  
            ' 
                + @TargetDB + 
                '.sys.columns C1  
                on FKC.parent_column_id=C1.column_id 
                and FKC.parent_object_id=C1.object_id 
                inner join  
            ' 
                + @TargetDB + 
                '.sys.columns C2 
                on FKC.referenced_column_id=C2.column_id 
                and FKC.referenced_object_id=C2.object_id                             
        ) 
    SELECT ''' 
                + @TargetDB + 
                ''',C.FK_NAME, 
                 C.FK_TABLE,             STUFF( ( SELECT '',''+ A.FK_COLUMN FROM 
    CTE a WHERE c.FK_NAME = a.FK_NAME and C.FK_TABLE=a.FK_TABLE FOR XML 
    PATH('''')),1 ,1, '''') AS FK_COLUMNS, 
                 C.PK_TABLE,                           
                 STUFF( ( SELECT '',''+ A.PK_Column FROM CTE a WHERE 
    c.FK_NAME = a.FK_NAME and C.PK_TABLE=a.PK_TABLE FOR XML 
    PATH('''')),1 ,1, '''') AS PK_COLUMNS  
    FROM CTE C 
    group by C.FK_NAME, 
                 C.FK_TABLE,              
                 C.PK_TABLE'
                )
    END;
    
    PRINT 'Print column mismatches!';
    PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDb) + 25);
    
    INSERT INTO #TAB_RESULTS (
        DATABASENAME
        ,TABLENAME
        ,COLUMNNAME
        ,DATATYPE
        ,NULLABLE
        ,REASON
        )
    SELECT @SourceDB AS DATABASENAME
        ,TABLENAME
        ,COLUMNNAME
        ,DATATYPE
        ,NULLABLE
        ,REASON
    FROM (
        SELECT TABLENAME
            ,COLUMNNAME
            ,DATATYPE
            ,NULLABLE
        FROM #TABLIST_SOURCE
        
        EXCEPT
        
        SELECT TS.TABLENAME
            ,TS.COLUMNNAME
            ,TS.DATATYPE
            ,TS.NULLABLE
        FROM #TABLIST_SOURCE TS
        INNER JOIN #TABLIST_TARGET TT ON TS.TABLENAME = TT.TABLENAME
            AND TS.COLUMNNAME = TT.COLUMNNAME
        ) TAB_NONMATCH
    CROSS JOIN (
        SELECT 'Missing Column' AS Reason
        ) Tab2
    
    UNION ALL
    
    SELECT @TargetDb AS DATABASENAME
        ,TABLENAME
        ,COLUMNNAME
        ,DATATYPE
        ,NULLABLE
        ,REASON
    FROM (
        SELECT TABLENAME
            ,COLUMNNAME
            ,DATATYPE
            ,NULLABLE
        FROM #TABLIST_TARGET
        
        EXCEPT
        
        SELECT TT.TABLENAME
            ,TT.COLUMNNAME
            ,TT.DATATYPE
            ,TT.NULLABLE
        FROM #TABLIST_TARGET TT
        INNER JOIN #TABLIST_SOURCE TS ON TS.TABLENAME = TT.TABLENAME
            AND TS.COLUMNNAME = TT.COLUMNNAME
        ) TAB_MATCH
    CROSS JOIN (
        SELECT 'Missing column ' AS Reason
        ) Tab2
    
    --NON MATCHING COLUMNS 
    INSERT INTO #TAB_RESULTS (
        DATABASENAME
        ,TABLENAME
        ,COLUMNNAME
        ,DATATYPE
        ,NULLABLE
        ,REASON
        )
    SELECT @SourceDB AS DATABASENAME
        ,TABLENAME
        ,COLUMNNAME
        ,DATATYPE
        ,NULLABLE
        ,REASON
    FROM (
        SELECT *
        FROM (
            SELECT TS.TABLENAME
                ,TS.COLUMNNAME
                ,TS.DATATYPE
                ,TS.NULLABLE
            FROM #TABLIST_SOURCE TS
            INNER JOIN #TABLIST_TARGET TT ON TS.TABLENAME = TT.
                TABLENAME
                AND TS.COLUMNNAME = TT.COLUMNNAME
            ) T
        
        EXCEPT
        
        (
            SELECT TABLENAME
                ,COLUMNNAME
                ,DATATYPE
                ,NULLABLE
            FROM #TABLIST_SOURCE
            
            INTERSECT
            
            SELECT TABLENAME
                ,COLUMNNAME
                ,DATATYPE
                ,NULLABLE
            FROM #TABLIST_TARGET
            )
        ) TT1
    CROSS JOIN (
        SELECT 'Definition not matching' AS REASON
        ) t
    
    UNION ALL
    
    SELECT @TargetDb AS DATABASENAME
        ,TABLENAME
        ,COLUMNNAME
        ,DATATYPE
        ,NULLABLE
        ,REASON
    FROM (
        SELECT *
        FROM (
            SELECT TT.TABLENAME
                ,TT.COLUMNNAME
                ,TT.DATATYPE
                ,TT.NULLABLE
            FROM #TABLIST_TARGET TT
            INNER JOIN #TABLIST_SOURCE TS ON TS.TABLENAME = TT.
                TABLENAME
                AND TS.COLUMNNAME = TT.COLUMNNAME
            ) T
        
        EXCEPT
        
        (
            SELECT TABLENAME
                ,COLUMNNAME
                ,DATATYPE
                ,NULLABLE
            FROM #TABLIST_TARGET
            
            INTERSECT
            
            SELECT TABLENAME
                ,COLUMNNAME
                ,DATATYPE
                ,NULLABLE
            FROM #TABLIST_SOURCE
            )
        ) TAB_NONMATCH
    CROSS JOIN (
        SELECT 'Definition not matching' AS REASON
        ) T;
    
    PRINT 'Print index mismatches!';
    PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDb) + 25);
    
    INSERT INTO #IDX_RESULTS (
        DATABASENAME
        ,TABLE_NAME
        ,IDX_NAME
        ,IDX_COLUMNS
        ,IDX_INCLUDED_COLUMNS
        ,IS_PRIMARY_KEY
        ,IS_UNIQUE
        ,REASON
        )
    SELECT @SourceDB AS DATABASENAME
        ,TABLE_NAME
        ,IDX_NAME
        ,IDX_COLUMNS
        ,IDX_INCLUDED_COLUMNS
        ,IS_PRIMARY_KEY
        ,IS_UNIQUE
        ,REASON
    FROM (
        SELECT TABLE_NAME
            ,IDX_NAME
            ,IDX_COLUMNS
            ,IDX_INCLUDED_COLUMNS
            ,IS_PRIMARY_KEY
            ,IS_UNIQUE
        FROM #IDXLIST_SOURCE
        
        EXCEPT
        
        SELECT TS.TABLE_NAME
            ,TS.IDX_NAME
            ,TS.IDX_COLUMNS
            ,TS.IDX_INCLUDED_COLUMNS
            ,TS.IS_PRIMARY_KEY
            ,TS.IS_UNIQUE
        FROM #IDXLIST_SOURCE TS
        INNER JOIN #IDXLIST_TARGET TT ON TS.TABLE_NAME = TT.TABLE_NAME
            AND TS.IDX_NAME = TT.IDX_NAME
        ) TAB_NONMATCH
    CROSS JOIN (
        SELECT 'Missing Index n' AS Reason
        ) Tab2
    
    UNION ALL
    
    SELECT @TargetDb AS DATABASENAME
        ,TABLE_NAME
        ,IDX_NAME
        ,IDX_COLUMNS
        ,IDX_INCLUDED_COLUMNS
        ,IS_PRIMARY_KEY
        ,IS_UNIQUE
        ,REASON
    FROM (
        SELECT TABLE_NAME
            ,IDX_NAME
            ,IDX_COLUMNS
            ,IDX_INCLUDED_COLUMNS
            ,IS_PRIMARY_KEY
            ,IS_UNIQUE
        FROM #IDXLIST_TARGET
        
        EXCEPT
        
        SELECT TT.TABLE_NAME
            ,TT.IDX_NAME
            ,TT.IDX_COLUMNS
            ,TT.IDX_INCLUDED_COLUMNS
            ,TT.IS_PRIMARY_KEY
            ,TT.IS_UNIQUE
        FROM #IDXLIST_TARGET TT
        INNER JOIN #IDXLIST_SOURCE TS ON TS.TABLE_NAME = TT.TABLE_NAME
            AND TS.IDX_NAME = TT.IDX_NAME
        ) TAB_MATCH
    CROSS JOIN (
        SELECT 'Missing index ' AS Reason
        ) Tab2
    
    --NON MATCHING INDEX 
    INSERT INTO #IDX_RESULTS (
        DATABASENAME
        ,TABLE_NAME
        ,IDX_NAME
        ,IDX_COLUMNS
        ,IDX_INCLUDED_COLUMNS
        ,IS_PRIMARY_KEY
        ,IS_UNIQUE
        ,REASON
        )
    SELECT @SourceDB AS DATABASENAME
        ,TABLE_NAME
        ,IDX_NAME
        ,IDX_COLUMNS
        ,IDX_INCLUDED_COLUMNS
        ,IS_PRIMARY_KEY
        ,IS_UNIQUE
        ,REASON
    FROM (
        SELECT *
        FROM (
            SELECT TS.TABLE_NAME
                ,TS.IDX_NAME
                ,TS.IDX_COLUMNS
                ,TS.IDX_INCLUDED_COLUMNS
                ,TS.IS_PRIMARY_KEY
                ,TS.IS_UNIQUE
            FROM #IDXLIST_SOURCE TS
            INNER JOIN #IDXLIST_TARGET TT ON TS.TABLE_NAME = TT.
                TABLE_NAME
                AND TS.IDX_NAME = TT.IDX_NAME
            ) T
        
        EXCEPT
        
        (
            SELECT TABLE_NAME
                ,IDX_NAME
                ,IDX_COLUMNS
                ,IDX_INCLUDED_COLUMNS
                ,IS_PRIMARY_KEY
                ,IS_UNIQUE
            FROM #IDXLIST_SOURCE
            
            INTERSECT
            
            SELECT TABLE_NAME
                ,IDX_NAME
                ,IDX_COLUMNS
                ,IDX_INCLUDED_COLUMNS
                ,IS_PRIMARY_KEY
                ,IS_UNIQUE
            FROM #IDXLIST_TARGET
            )
        ) TT1
    CROSS JOIN (
        SELECT 'Definition not matching' AS REASON
        ) t
    
    UNION ALL
    
    SELECT @TargetDb AS DATABASENAME
        ,TABLE_NAME
        ,IDX_NAME
        ,IDX_COLUMNS
        ,IDX_INCLUDED_COLUMNS
        ,IS_PRIMARY_KEY
        ,IS_UNIQUE
        ,REASON
    FROM (
        SELECT *
        FROM (
            SELECT TT.TABLE_NAME
                ,TT.IDX_NAME
                ,TT.IDX_COLUMNS
                ,TT.IDX_INCLUDED_COLUMNS
                ,TT.IS_PRIMARY_KEY
                ,TT.IS_UNIQUE
            FROM #IDXLIST_TARGET TT
            INNER JOIN #IDXLIST_SOURCE TS ON TS.TABLE_NAME = TT.
                TABLE_NAME
                AND TS.IDX_NAME = TT.IDX_NAME
            ) T
        
        EXCEPT
        
        (
            SELECT TABLE_NAME
                ,IDX_NAME
                ,IDX_COLUMNS
                ,IDX_INCLUDED_COLUMNS
                ,IS_PRIMARY_KEY
                ,IS_UNIQUE
            FROM #IDXLIST_TARGET
            
            INTERSECT
            
            SELECT TABLE_NAME
                ,IDX_NAME
                ,IDX_COLUMNS
                ,IDX_INCLUDED_COLUMNS
                ,IS_PRIMARY_KEY
                ,IS_UNIQUE
            FROM #IDXLIST_SOURCE
            )
        ) TAB_NONMATCH
    CROSS JOIN (
        SELECT 'Definition not matching' AS REASON
        ) T;
    
    PRINT 'Print key mismatches!';
    PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDb) + 25);
    
    INSERT INTO #FK_RESULTS (
        DATABASENAME
        ,FK_NAME
        ,FK_TABLE
        ,FK_COLUMNS
        ,PK_TABLE
        ,PK_COLUMNS
        ,REASON
        )
    SELECT @SourceDB AS DATABASENAME
        ,FK_NAME
        ,FK_TABLE
        ,FK_COLUMNS
        ,PK_TABLE
        ,PK_COLUMNS
        ,REASON
    FROM (
        SELECT FK_NAME
            ,FK_TABLE
            ,FK_COLUMNS
            ,PK_TABLE
            ,PK_COLUMNS
        FROM #FKLIST_SOURCE
        
        EXCEPT
        
        SELECT TS.FK_NAME
            ,TS.FK_TABLE
            ,TS.FK_COLUMNS
            ,TS.PK_TABLE
            ,TS.PK_COLUMNS
        FROM #FKLIST_SOURCE TS
        INNER JOIN #FKLIST_TARGET TT ON TS.FK_NAME = TT.FK_NAME
        ) TAB_NONMATCH
    CROSS JOIN (
        SELECT 'Missing Index n' AS Reason
        ) Tab2
    
    UNION ALL
    
    SELECT @TargetDb AS DATABASENAME
        ,FK_NAME
        ,FK_TABLE
        ,FK_COLUMNS
        ,PK_TABLE
        ,PK_COLUMNS
        ,REASON
    FROM (
        SELECT FK_NAME
            ,FK_TABLE
            ,FK_COLUMNS
            ,PK_TABLE
            ,PK_COLUMNS
        FROM #FKLIST_TARGET
        
        EXCEPT
        
        SELECT TT.FK_NAME
            ,TT.FK_TABLE
            ,TT.FK_COLUMNS
            ,TT.PK_TABLE
            ,TT.PK_COLUMNS
        FROM #FKLIST_TARGET TT
        INNER JOIN #FKLIST_SOURCE TS ON TS.FK_NAME = TT.FK_NAME
        ) TAB_MATCH
    CROSS JOIN (
        SELECT 'Missing key' AS Reason
        ) Tab2
    
    --NON MATCHING Keys 
    INSERT INTO #FK_RESULTS (
        DATABASENAME
        ,FK_NAME
        ,FK_TABLE
        ,FK_COLUMNS
        ,PK_TABLE
        ,PK_COLUMNS
        ,REASON
        )
    SELECT @SourceDB AS DATABASENAME
        ,FK_NAME
        ,FK_TABLE
        ,FK_COLUMNS
        ,PK_TABLE
        ,PK_COLUMNS
        ,REASON
    FROM (
        SELECT *
        FROM (
            SELECT TS.FK_NAME
                ,TS.FK_TABLE
                ,TS.FK_COLUMNS
                ,TS.PK_TABLE
                ,TS.PK_COLUMNS
            FROM #FKLIST_SOURCE TS
            INNER JOIN #FKLIST_TARGET TT ON TS.FK_NAME = TT.FK_NAME
            ) T
        
        EXCEPT
        
        (
            SELECT FK_NAME
                ,FK_TABLE
                ,FK_COLUMNS
                ,PK_TABLE
                ,PK_COLUMNS
            FROM #FKLIST_SOURCE
            
            INTERSECT
            
            SELECT FK_NAME
                ,FK_TABLE
                ,FK_COLUMNS
                ,PK_TABLE
                ,PK_COLUMNS
            FROM #FKLIST_TARGET
            )
        ) TT1
    CROSS JOIN (
        SELECT 'Definition not matching' AS REASON
        ) t
    
    UNION ALL
    
    SELECT @TargetDb AS DATABASENAME
        ,FK_NAME
        ,FK_TABLE
        ,FK_COLUMNS
        ,PK_TABLE
        ,PK_COLUMNS
        ,REASON
    FROM (
        SELECT *
        FROM (
            SELECT TT.FK_NAME
                ,TT.FK_TABLE
                ,TT.FK_COLUMNS
                ,TT.PK_TABLE
                ,TT.PK_COLUMNS
            FROM #FKLIST_TARGET TT
            INNER JOIN #FKLIST_SOURCE TS ON TS.FK_NAME = TT.FK_NAME
            ) T
        
        EXCEPT
        
        (
            SELECT FK_NAME
                ,FK_TABLE
                ,FK_COLUMNS
                ,PK_TABLE
                ,PK_COLUMNS
            FROM #FKLIST_TARGET
            
            INTERSECT
            
            SELECT FK_NAME
                ,FK_TABLE
                ,FK_COLUMNS
                ,PK_TABLE
                ,PK_COLUMNS
            FROM #FKLIST_SOURCE
            )
        ) TAB_NONMATCH
    CROSS JOIN (
        SELECT 'Definition not matching' AS REASON
        ) T;
    
    --Print Final Results     
    SELECT *
    FROM #TAB_RESULTS
    
    SELECT *
    FROM #IDX_RESULTS
    
    SELECT *
    FROM #FK_RESULTS

    SQL Script Output Screenshot

    image

    User Level to execute

      200

      Hope you will like to Script of 3 and last type of C. Compare Database.

      If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.

    If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog :

    Link Resource Website

    For More information related to BI World visit my Mentalist Blog

    SQL Server Mentalist >> SQL Learning Blog

    Business Intelligence Mentalist >> BI World

    Infographic Mentalist >> Image worth explaining thousand Words

    Microsoft Mentalist >> MVC,ASP.NET, WCF & LinQ

    DBA Mentalist >>Advance SQL Server Blog

    Microsoft BI Mentalist >> MS BI Development Update

    Connect With me on

    | FaceBook |Twitter | linkedIn| Google+ | WordPress | RSS |

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: