Get all database, table and column names and column definitions I need to check the data is identical between SQL Server.

For that I need following details. Let me know if this is possible.


All Database Names
table Names
column Names
Column Max Length in a table

╔══════════╦══════════╦══════════════╦══════╗
║ Database ║  Table   ║    column    ║ Size ║
╠══════════╬══════════╬══════════════╬══════╣
║ IBM      ║ Employee ║ EmployeeId   ║    5 ║
║ IBM      ║ Employee ║ EmployeeName ║   49 ║
║ IBM      ║ Project  ║ ProjectID    ║    4 ║
║ IBM      ║ Project  ║ ProjectName  ║  110 ║
║ TCS      ║ Employee ║ EmployeeId   ║    6 ║
║ TCS      ║ Employee ║ EmployeeName ║   51 ║
║ TCS      ║ Project  ║ ProjectID    ║    5 ║
║ TCS      ║ Project  ║ ProjectName  ║  226 ║
╚══════════╩══════════╩══════════════╩══════╝
I have an script that I used to use.
this is to compare 2 databases, in the example I am comparing bocss2 with tablebackups.

Now I only use the visual studio schema comparison tool as suggested in the comments.

It is not even properly formatted, it is not a complete, ready, free from bugs solution, but it would at least give you some ideas.

I still would like to suggest you use the visual studio community edition

--sp_CompareDb @SourceDB='Bocss2',@TargetDb='Tablebackups'


----DROP PROCEDURE sp_CompareDb

--USE MASTER
--GO

--CREATE PROC sp_CompareDb(  
--                            @SourceDB SYSNAME, 
--                            @TargetDb SYSNAME 
--                            )                        
--AS 


/* 
DECLARE @SourceDB SYSNAME='DB1',  
                  @TargetDb SYSNAME='DB2' 
*/ 




DECLARE @SourceDB SYSNAME='Bocss2',  
        @TargetDb SYSNAME='Tablebackups' 

  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(@TargetDb) 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)      
  -----------------------------------------------------------------------------------------  
    -- Create temp tables needed to hold the db structure  
    -----------------------------------------------------------------------------------------      

    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&gt;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&gt;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 ORDER BY a.ColumnName  FOR XML PATH('''')),1 ,1, '''') AS COLUMNS  
       ,STUFF( ( SELECT '',''+ a.IncludedColumn FROM CTE a WHERE c.IndexId = a.IndexId  ORDER BY a.ColumnName  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, C.IndexName ' ); 


    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  ORDER BY a.ColumnName  FOR XML PATH('''')),1 ,1, '''') AS Columns  
       ,STUFF( ( SELECT '',''+ a.IncludedColumn FROM CTE a WHERE c.IndexId = a.IndexId  ORDER BY a.ColumnName 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, C.IndexName '); 


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

order 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

order 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 
--where tablename= 'tblBOrder'

SELECT * FROM #IDX_RESULTS 
where TABLE_NAME = 'tblBorder'

--SELECT * FROM #FK_RESULTS 

--select * from #FKLIST_TARGET 


select * FROM #IDXLIST_SOURCE
go
select * FROM #IDXLIST_target
go