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(@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 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
Saturday, 28 March 2015
Compare two databases for objects differences
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment