projenizi birden fazla sitede kullanıyorsanız, dosyaları güncelleme sırasında bir çok sorunla karşılaşmışsınmızdır. bunlardan en yaygın olanı veri tabanında eksik alan veya tablonun olmaması hatasıdır. eksik alanları, tabloları ve view leri gösteren Stored Procedure aşağıdadır.

CREATE PROC sp_CompareDb(
@KaynakDB SYSNAME,
@HedefDB SYSNAME
)
AS
/*
DECLARE @KaynakDB SYSNAME='DB1',
@HedefDB SYSNAME='DB2'
*/
SET nocount ON
SET ansi_warnings ON
SET ansi_nulls ON

DECLARE @sqlStr VARCHAR(8000)
SET @KaynakDB= Rtrim(Ltrim(@KaynakDB))
IF DB_ID(@KaynakDB) IS NULL
BEGIN
PRINT 'Hata: VeriTabanı Bulunamadı '+ @KaynakDB +'!!!'
RETURN
END

SET @HedefDB= Rtrim(Ltrim(@HedefDB))
IF DB_ID(@KaynakDB) IS NULL
BEGIN
PRINT 'Hata: VeriTabanı Bulunamadı '+ @HedefDB +'!!!'
RETURN
END

PRINT Replicate('-', Len(@KaynakDB) + Len(@HedefDB) + 25)
PRINT 'Comparing databases ' + @KaynakDB + ' and ' + @HedefDB
PRINT Replicate('-', Len(@KaynakDB) + Len(@HedefDB) + 25)
-----------------------------------------------------------------------------------------
-- Geçici olarak tablo oluştur
-----------------------------------------------------------------------------------------

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(@KaynakDB) + Len(@HedefDB) + 25);

BEGIN
INSERT INTO #TABLIST_SOURCE(DATABASENAME, TABLENAME,COLUMNNAME,DATATYPE,NULLABLE)
EXEC('SELECT '''+@KaynakDB +''', 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 '+@KaynakDB+'.INFORMATION_SCHEMA.TABLES T
INNER JOIN '+@KaynakDB+'.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 '+@KaynakDB+'.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 '''+@HedefDB +''', 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 '+@HedefDB+'.INFORMATION_SCHEMA.TABLES T
INNER JOIN '+@HedefDB+'.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 '+@HedefDB+'.sys.types TY
ON C.DATA_TYPE =TY.name
ORDER BY TABLENAME, COLUMNNAME,C.ORDINAL_POSITION');
PRINT 'Getting index list!';
PRINT Replicate('-', Len(@KaynakDB) + Len(@HedefDB) + 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 '+@KaynakDB+'.sys.indexes i
INNER JOIN '+@KaynakDB+'.sys.index_columns ic
ON i.index_id = ic.index_id
AND i.object_id = ic.object_id
INNER JOIN '+@KaynakDB+'.sys.columns c
ON ic.column_id = c.column_id
AND i.object_id = c.object_id
INNER JOIN '+@KaynakDB+'.sys.tables t
ON i.object_id = t.object_id
)
SELECT '''+@KaynakDB+''',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 '+@HedefDB+'.sys.indexes i
INNER JOIN '+@HedefDB+'.sys.index_columns ic
ON i.index_id = ic.index_id
AND i.object_id = ic.object_id
INNER JOIN '+@HedefDB+'.sys.columns c
ON ic.column_id = c.column_id
AND i.object_id = c.object_id
INNER JOIN '+@HedefDB+'.sys.tables t
ON i.object_id = t.object_id
)
SELECT '''+@HedefDB+''',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(@KaynakDB) + Len(@HedefDB) + 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('''+@HedefDB+''')) PK_TABLE,
C1.name PK_COLUMN,
object_name(FK.referenced_object_id,db_id('''+@HedefDB+'''))FK_TABLE,
C2.name FK_COLUMN,
FK.name FK_NAME
from
'+@KaynakDB+'.sys.foreign_keys FK
inner join
'+@KaynakDB+'.sys.foreign_key_columns FKC
on FK.object_id=FKC.constraint_object_id
inner join
'+@KaynakDB+'.sys.columns C1
on FKC.parent_column_id=C1.column_id
and FKC.parent_object_id=C1.object_id
inner join
'+@KaynakDB+'.sys.columns C2
on FKC.referenced_column_id=C2.column_id
and FKC.referenced_object_id=C2.object_id
)
SELECT '''+@KaynakDB+''',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('''+@HedefDB+''')) PK_TABLE,
C1.name PK_COLUMN,
object_name(FK.referenced_object_id,db_id('''+@HedefDB+'''))FK_TABLE,
C2.name FK_COLUMN,
FK.name FK_NAME
from
'+@HedefDB+'.sys.foreign_keys FK
inner join
'+@HedefDB+'.sys.foreign_key_columns FKC
on FK.object_id=FKC.constraint_object_id
inner join
'+@HedefDB+'.sys.columns C1
on FKC.parent_column_id=C1.column_id
and FKC.parent_object_id=C1.object_id
inner join
'+@HedefDB+'.sys.columns C2
on FKC.referenced_column_id=C2.column_id
and FKC.referenced_object_id=C2.object_id
)
SELECT '''+@HedefDB+''',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(@KaynakDB) + Len(@HedefDB) + 25);

INSERT INTO #TAB_RESULTS(DATABASENAME, TABLENAME,COLUMNNAME,DATATYPE,NULLABLE,REASON)
SELECT @KaynakDB 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 @HedefDB 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
@KaynakDB 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 @HedefDB 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(@KaynakDB) + Len(@HedefDB) + 25);
INSERT INTO #IDX_RESULTS(DATABASENAME, TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE,REASON)
SELECT @KaynakDB 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 @HedefDB 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
@KaynakDB 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 @HedefDB 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(@KaynakDB) + Len(@HedefDB) + 25);

INSERT INTO #FK_RESULTS(DATABASENAME, FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS,REASON)
SELECT @KaynakDB 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 @HedefDB 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
@KaynakDB 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 @HedefDB 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;

--Sonuçları yazdır

SELECT * FROM #TAB_RESULTS
SELECT * FROM #IDX_RESULTS
SELECT * FROM #FK_RESULTS