SQL Server’da İki Veritabanını Karşılaştıran Script

Herkese merhaba,

Bu yazıda SQL Server’da iki veritabanını karşılaştıran script hakkında bilgi vereceğim.

SQL Server’da bazı durumlarda iki veritabanını script ile karşılaştırmak isteyebilirsiniz.

Bunu yapan çeşitli yazılımlar da var fakat aşağıdaki script yardımıyla da kolaylıkla yapabilirsiniz.

Her ne kadar çok fazla tablo içeren veritabanları üzerinde denememiş olsamda ufak ve orta ölçekli veritabanları için kullanışlı bir script olduğunu düşünmekteyim.

İlk olarak aşağıdaki prosedürü kendi serverınızda ilgili veritabanı üzerine kurmalısınız.

CREATE PROC IkiDBKarsilastir
(
    @KaynakDB SYSNAME,
    @HedefDB SYSNAME
)
AS
BEGIN

    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 'Veritabanlarını karşılaştırma ' + @KaynakDB + ' ve ' + @HedefDB;
    PRINT REPLICATE('-', LEN(@KaynakDB) + LEN(@HedefDB) + 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 'Tablo ve Kolon listeleri!';
    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 'Indeks listeleri!';
        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,ic.key_ordinal 
												 ,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 (SELECT object_id,name FROM ' + @KaynakDB + '.sys.tables  union SELECT object_id,name FROM ' + @KaynakDB + '.sys.views)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 ORDER BY key_ordinal FOR XML PATH('''')),1 ,1, '''') AS COLUMNS
				 ,STUFF( ( SELECT '',''+ a.IncludedColumn FROM CTE a WHERE c.IndexId = a.IndexId ORDER BY key_ordinal,IncludedColumn 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,ic.key_ordinal 
						 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 (SELECT object_id,name FROM ' + @HedefDB + '.sys.tables  union SELECT object_id,name FROM ' + @HedefDB + '.sys.views)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 ORDER BY key_ordinal FOR XML PATH('''')),1 ,1, '''') AS Columns 
				 ,STUFF( ( SELECT '',''+ a.IncludedColumn FROM CTE a WHERE c.IndexId = a.IndexId ORDER BY key_ordinal,IncludedColumn 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 'Foreign key listeleri!';
        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(''' + @KaynakDB + ''')) PK_TABLE,	 
							C1.name PK_COLUMN,
				object_name(FK.referenced_object_id,db_id(''' + @KaynakDB + '''))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 'Kolon uyumsuzlukları!';
    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 'Eksik Kolonlar' 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 'Eksik Kolonlar ' AS Reason) Tab2;


    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 'Eşleşmeyen tanımlamalar' 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 'Eşleşmeyen tanımlamalar' AS REASON) T;

    PRINT 'Index uyumsuzlukları!';
    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 'Eksik Indeks' 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 'Eksik Indeks ' AS Reason) Tab2;


    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 'Eşleşmeyen tanımlamalar' 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 'Eşleşmeyen tanımlamalar' AS REASON) T;


    PRINT 'Anahtar alan uyumsuzlukları!';
    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 'Eksik Indeks ' 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 'Eksik Anahtar Alan' AS Reason) Tab2;


    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 'Eşleşmeyen tanımlamalar' 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 'Eşleşmeyen tanımlamalar' AS REASON) T;



    SELECT *
    FROM #TAB_RESULTS;
    SELECT *
    FROM #IDX_RESULTS;
    SELECT *
    FROM #FK_RESULTS;
END;
GO



--Kullanımı

EXEC IkiDBKarsilastir @KaynakDB = 'Northwind', @HedefDB = 'Northwind2';

Prosedürü oluşturduktan sonra hedef ve kaynak alanlarına ilgili veritabanı isimlerini yazmalısınız.

Ben örnek olarak Northwind veritabanı üzerinde çalıştım. Northwind ve Northwind2

İlgili veritabanı adlarını parametre kısımlarına yazdıktan sonra prosedürü çalıştırdığınızda hangi kolon, hangi indeks eksik vs bize gösteriyor.

Görüldüğü üzere iki veritabanı karşılaştırılmış oldu.

Herkese çalışma hayatında ve yaşamında başarılar kolaylıklar.

SQL Server Eğitimleri Kanalına Abone Olun

Şunlar makaleler de hoşunuza gidebilir

Ads Blocker Image Powered by Code Help Pro
Reklam Eklentisi Kullanıyorsunuz

Lütfen bizlere destek için reklam eklentisi olmadan sitemizi ziyaret edin.

Şimdiden teşekkür ederiz.