Kod:
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