0

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 ║
    ╚══════════╩══════════╩══════════════╩══════╝
    
  • 4
    Don't do that by hand, use a tool. Visual Studio has data compare, for instance. Visual Studio community edition is free. RedGate has a data compare product. Seriously, don't do that by hand. – spaghettidba Sep 21 '16 at 7:40
1

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>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 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
0

You can try this query.

CREATE TABLE #tmp(
[dbname] [nvarchar](128) NULL,
[table_schema] [nvarchar](128) NULL,
[column_name] [sysname] NULL,
[data_type] [nvarchar](128) NULL,
[character_maximum_length] [int] NULL,
[is_nullable] [varchar](3) NULL
) 

insert into #tmp
exec sp_msforeachdb 'use ? select db_name(),table_schema,column_name,data_type,character_maximum_length,is_nullable from information_schema.columns'

select * from #temp

drop table #tmp

Thanks

  • Not the Datatype length, Need data Length. – Ragul Sep 21 '16 at 8:55
  • This piece of code looks good can someone edit it for me to get column data size ! – Ragul Sep 22 '16 at 9:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.