2 tablo arasındaki Veri Farklarını bulma

[adelphiforumz]

Selamlar 
2 Tablo arasındaki verileri karşılaştıran ve farklarını listeleyen bir SP yi AI yardımı ile aşağıdaki şekilde hazırladım.
isteyen üzerinde biraz değişiklik yaparak kullanmak isterse kodlar burada 
bu hali şimdilik benim işimi görmüş durumda.

Kod: (Select All)

CREATE OR ALTER PROCEDURE dbo.usp_TabloVeriKarsilastir_Dinamik
    @TabloA             nvarchar(512),   -- ör: N'DB_A.dbo.Urun'
    @TabloB             nvarchar(512),   -- ör: N'DB_B_dbo.Urun'
    @KeyColumn          sysname,         -- ör: N'IdUrun'
    @ExcludeColumns     nvarchar(MAX) = NULL,  -- CSV: 'Miktar'
    @IncludeOnlyColumns nvarchar(MAX) = NULL   -- CSV: 'UrunAdi,Fiyat'
AS
BEGIN
    SET NOCOUNT ON;

    /* 0) Geçersiz kombinasyon kontrolü */
    IF NULLIF(LTRIM(RTRIM(@ExcludeColumns)), N'') IS NOT NULL
       AND NULLIF(LTRIM(RTRIM(@IncludeOnlyColumns)), N'') IS NOT NULL
    BEGIN
        RAISERROR(N'@IncludeOnlyColumns ve @ExcludeColumns aynı anda kullanılamaz.', 16, 1);
        RETURN;
    END

    /* 1) Dört-parçalı adları çöz: [Sunucu].[Veritabanı].[Şema].[Obje] */
    DECLARE @A_srv sysname = PARSENAME(@TabloA,4),
            @A_db  sysname = PARSENAME(@TabloA,3),
            @A_sc  sysname = PARSENAME(@TabloA,2),
            @A_obj sysname = PARSENAME(@TabloA,1);

    DECLARE @B_srv sysname = PARSENAME(@TabloB,4),
            @B_db  sysname = PARSENAME(@TabloB,3),
            @B_sc  sysname = PARSENAME(@TabloB,2),
            @B_obj sysname = PARSENAME(@TabloB,1);

    IF @A_sc IS NULL OR @A_obj IS NULL OR @B_sc IS NULL OR @B_obj IS NULL
    BEGIN
        RAISERROR(N'Tablo adları en az [Şema].[Obje] biçiminde olmalıdır.', 16, 1);
        RETURN;
    END

    DECLARE @KeyQ sysname = QUOTENAME(@KeyColumn);

    /* 2) CSV’leri STRING_SPLIT kullanmadan parçala -> table variable */
    DECLARE @Ex TABLE (name sysname PRIMARY KEY);
    DECLARE @In TABLE (name sysname PRIMARY KEY);

    DECLARE @s nvarchar(MAX), @p int, @tok sysname;

    -- Exclude doldur
    SET @s = LTRIM(RTRIM(ISNULL(@ExcludeColumns,N'')));
    WHILE LEN(@s) > 0
    BEGIN
        SET @p = CHARINDEX(',', @s);
        IF @p = 0
        BEGIN
            SET @tok = CONVERT(sysname, LTRIM(RTRIM(@s)));
            SET @s = N'';
        END
        ELSE
        BEGIN
            SET @tok = CONVERT(sysname, LTRIM(RTRIM(SUBSTRING(@s,1,@p-1))));
            SET @s   = SUBSTRING(@s, @p+1, 2147483647);
        END
        IF @tok IS NOT NULL AND @tok <> N'' INSERT INTO @Ex(name) VALUES(@tok);
    END

    -- IncludeOnly doldur
    SET @s = LTRIM(RTRIM(ISNULL(@IncludeOnlyColumns,N'')));
    WHILE LEN(@s) > 0
    BEGIN
        SET @p = CHARINDEX(',', @s);
        IF @p = 0
        BEGIN
            SET @tok = CONVERT(sysname, LTRIM(RTRIM(@s)));
            SET @s = N'';
        END
        ELSE
        BEGIN
            SET @tok = CONVERT(sysname, LTRIM(RTRIM(SUBSTRING(@s,1,@p-1))));
            SET @s   = SUBSTRING(@s, @p+1, 2147483647);
        END
        IF @tok IS NOT NULL AND @tok <> N'' INSERT INTO @In(name) VALUES(@tok);
    END

    /* 3) Kolon metadatasını çek */
    IF OBJECT_ID('tempdb..#ACols') IS NOT NULL DROP TABLE #ACols;
    IF OBJECT_ID('tempdb..#BCols') IS NOT NULL DROP TABLE #BCols;

    CREATE TABLE #ACols(
        name sysname,
        column_id int,
        system_type_id int,
        user_type_id int,
        type_name sysname,
        max_length smallint,
        precision tinyint,
        scale tinyint,
        is_nullable bit,
        collation_name sysname NULL
    );
    CREATE TABLE #BCols(
        name sysname,
        column_id int,
        system_type_id int,
        user_type_id int,
        type_name sysname,
        max_length smallint,
        precision tinyint,
        scale tinyint,
        is_nullable bit,
        collation_name sysname NULL
    );

    DECLARE @sysA nvarchar(400) =
        COALESCE(QUOTENAME(@A_srv)+N'.',N'') + COALESCE(QUOTENAME(@A_db)+N'.',N'') + N'sys.';
    DECLARE @sysB nvarchar(400) =
        COALESCE(QUOTENAME(@B_srv)+N'.',N'') + COALESCE(QUOTENAME(@B_db)+N'.',N'') + N'sys.';

    DECLARE @sqlA nvarchar(MAX) = N'
        SELECT c.name, c.column_id, t.system_type_id, c.user_type_id, t.name AS type_name,
               c.max_length, c.[precision], c.scale, c.is_nullable, c.collation_name
        FROM ' + @sysA + N'columns AS c
        JOIN ' + @sysA + N'types   AS t ON c.user_type_id = t.user_type_id
        JOIN ' + @sysA + N'objects AS o ON c.object_id = o.object_id
        JOIN ' + @sysA + N'schemas AS s ON o.schema_id = s.schema_id
        WHERE o.type IN (''U'',''V'') AND s.name=@sc AND o.name=@obj;';

    DECLARE @sqlB nvarchar(MAX) = N'
        SELECT c.name, c.column_id, t.system_type_id, c.user_type_id, t.name AS type_name,
               c.max_length, c.[precision], c.scale, c.is_nullable, c.collation_name
        FROM ' + @sysB + N'columns AS c
        JOIN ' + @sysB + N'types   AS t ON c.user_type_id = t.user_type_id
        JOIN ' + @sysB + N'objects AS o ON c.object_id = o.object_id
        JOIN ' + @sysB + N'schemas AS s ON o.schema_id = s.schema_id
        WHERE o.type IN (''U'',''V'') AND s.name=@sc AND o.name=@obj;';

    INSERT INTO #ACols
    EXEC sp_executesql @sqlA, N'@sc sysname, @obj sysname', @sc=@A_sc, @obj=@A_obj;

    INSERT INTO #BCols
    EXEC sp_executesql @sqlB, N'@sc sysname, @obj sysname', @sc=@B_sc, @obj=@B_obj;

    /* 4) Otomatik hariç tutulacak tipler */
    IF OBJECT_ID('tempdb..#BadTypes') IS NOT NULL DROP TABLE #BadTypes;
    CREATE TABLE #BadTypes(type_name sysname PRIMARY KEY);
    INSERT INTO #BadTypes VALUES
        (N'timestamp'),(N'rowversion'),(N'text'),(N'ntext'),(N'image'),(N'sql_variant');

    /* 5) Ortak ve karşılaştırılabilir kolonların listesini hazırla */
    IF OBJECT_ID('tempdb..#Cols') IS NOT NULL DROP TABLE #Cols;
    CREATE TABLE #Cols(ord int IDENTITY(1,1) PRIMARY KEY, name sysname, is_string bit);

    -- Key her iki tabloda var mı?
    IF NOT EXISTS (SELECT 1 FROM #ACols WHERE name=@KeyColumn)
       OR NOT EXISTS (SELECT 1 FROM #BCols WHERE name=@KeyColumn)
    BEGIN
        RAISERROR(N'Anahtar kolon her iki tabloda da bulunamadı: %s', 16, 1, @KeyColumn);
        RETURN;
    END

    -- IncludeOnly verilmişse: listedeki her kolon iki tabloda da var mı?
    IF EXISTS (SELECT 1 FROM @In)
    BEGIN
        IF EXISTS (
            SELECT i.name
            FROM @In i
            WHERE NOT EXISTS (SELECT 1 FROM #ACols WHERE name=i.name)
               OR NOT EXISTS (SELECT 1 FROM #BCols WHERE name=i.name)
        )
        BEGIN
            DECLARE @missing nvarchar(MAX) = N'';
            SELECT @missing = COALESCE(@missing + N',' , N'') + i.name
            FROM @In i
            WHERE NOT EXISTS (SELECT 1 FROM #ACols WHERE name=i.name)
               OR NOT EXISTS (SELECT 1 FROM #BCols WHERE name=i.name);
            RAISERROR(N'@IncludeOnlyColumns içindeki şu kolon(lar) iki tabloda da yok: %s', 16, 1, @missing);
            RETURN;
        END
    END

    -- Ortak ve kıyaslanabilir kolonlar (ad, tip adı, uzunluk/precision/scale eşleşmeli)
    INSERT INTO #Cols(name, is_string)
    SELECT a.name,
           CASE WHEN a.type_name IN (N'char',N'varchar',N'nchar',N'nvarchar') THEN 1 ELSE 0 END
    FROM #ACols a
    JOIN #BCols b
      ON b.name = a.name
     AND a.type_name    = b.type_name
     AND a.max_length   = b.max_length
     AND a.[precision]  = b.[precision]
     AND a.scale        = b.scale
    LEFT JOIN #BadTypes bt ON bt.type_name = a.type_name
    LEFT JOIN @Ex ex ON ex.name = a.name
    WHERE bt.type_name IS NULL
      AND a.name <> @KeyColumn
      AND (
            NOT EXISTS(SELECT 1 FROM @In)
            OR a.name IN (SELECT name FROM @In)
          )
    ORDER BY a.column_id;

    /* 6) Liste ve ifadeleri oluştur (karakter kolonlara COLLATE + ALIAS uygula) */
    DECLARE @KeyIsString bit = CASE WHEN EXISTS (
        SELECT 1 FROM #ACols WHERE name=@KeyColumn AND type_name IN (N'char',N'varchar',N'nchar',N'nvarchar')
    ) THEN 1 ELSE 0 END;

    DECLARE @NonKeyListAExp nvarchar(MAX) = N'';
    SELECT @NonKeyListAExp = STUFF((
        SELECT N',' + CASE WHEN is_string=1
                           THEN QUOTENAME(name) + N' COLLATE DATABASE_DEFAULT AS ' + QUOTENAME(name)
                           ELSE QUOTENAME(name) + N' AS ' + QUOTENAME(name) END
        FROM #Cols
        ORDER BY ord
        FOR XML PATH(''), TYPE).value('.','nvarchar(max)'),1,1,N'');

    DECLARE @KeyExprA nvarchar(400) = CASE WHEN @KeyIsString=1
                                           THEN @KeyQ + N' COLLATE DATABASE_DEFAULT AS __k__'
                                           ELSE @KeyQ + N' AS __k__' END;

    DECLARE @SelectListA nvarchar(MAX) = @KeyExprA + CASE WHEN @NonKeyListAExp <> N'' THEN N',' + @NonKeyListAExp ELSE N'' END;
    DECLARE @SelectListB nvarchar(MAX) = @SelectListA; -- ifadeler simetrik olmalı

    /* 7) Tam nitelikli tablo adları */
    DECLARE @TA nvarchar(1000) =
        COALESCE(QUOTENAME(@A_srv)+N'.',N'') + COALESCE(QUOTENAME(@A_db)+N'.',N'') + QUOTENAME(@A_sc) + N'.' + QUOTENAME(@A_obj);
    DECLARE @TB nvarchar(1000) =
        COALESCE(QUOTENAME(@B_srv)+N'.',N'') + COALESCE(QUOTENAME(@B_db)+N'.',N'') + QUOTENAME(@B_sc) + N'.' + QUOTENAME(@B_obj);

    /* 8) Join ifadeleri (string anahtar ise COLLATE uygula) */
    DECLARE @JoinA nvarchar(500) = N'A.' + @KeyQ + CASE WHEN @KeyIsString=1 THEN N' COLLATE DATABASE_DEFAULT' ELSE N'' END + N' = F.__k__';
    DECLARE @JoinB nvarchar(500) = N'B.' + @KeyQ + CASE WHEN @KeyIsString=1 THEN N' COLLATE DATABASE_DEFAULT' ELSE N'' END + N' = F.__k__';

    /* 9) Farklı anahtarları çıkar ve sonuçları döndür — alias'lı + subquery'li */
    DECLARE @sql nvarchar(MAX) = N'
;WITH FarkliAnahtarlar AS (
    SELECT DISTINCT __k__
    FROM (
        SELECT * FROM (
            SELECT ' + @SelectListA + N' FROM ' + @TA + N'
        ) AS A1
        EXCEPT
        SELECT * FROM (
            SELECT ' + @SelectListB + N' FROM ' + @TB + N'
        ) AS B1

        UNION ALL

        SELECT * FROM (
            SELECT ' + @SelectListB + N' FROM ' + @TB + N'
        ) AS B2
        EXCEPT
        SELECT * FROM (
            SELECT ' + @SelectListA + N' FROM ' + @TA + N'
        ) AS A2
    ) D
)
SELECT F.__k__ AS __k__, 1 AS KaynakOrder, ''A'' AS Kaynak, A.*
FROM ' + @TA + N' AS A
JOIN FarkliAnahtarlar F
  ON ' + @JoinA + N'

UNION ALL

SELECT F.__k__ AS __k__, 2 AS KaynakOrder, ''B'' AS Kaynak, B.*
FROM ' + @TB + N' AS B
JOIN FarkliAnahtarlar F
  ON ' + @JoinB + N'

ORDER BY __k__, KaynakOrder;';

  print @sql

    EXEC sys.sp_executesql @sql;
END
GO

Kod: (Select All)

exec usp_TabloVeriKarsilastir_Dinamik
   @TabloA = N'DB_A.dbo.Urun'
   ,@TabloB = N'DB_Bdbo.Urun'
   ,@KeyColumn = N'IdUrun'
   ,@ExcludeColumns = N'Fiyat,Miktar'
   ,@IncludeOnlyColumns = N''