Toplu CheckIdent İşlemleri

Daha önceki yazımda IDENTITY kolon üzerinde RESEED işleminin yapılma nedeni ve yapılış şeklinden bahsetmiştim.

Bazı durumlarda TEST, DEV  veya UAT ortamlarında veritabanı bazında metadata eşitleme isteği gelirse eşitlemeniz sonrasında eşitlediğiniz bütün tablolarda identity alanın resetlenip 0’dan başladığını durumlar oluşur. Veritabanındaki tablolar dolu olmasına rağmen identity alanın resetlenmesi sonucu yaptığınız her işlemde aşağıdaki şekilde hatayla karşılaşırsınız.

Msg 2627, Level 14, State 1, Line 17
Violation of PRIMARY KEY constraint ‘PK__Deneme_ID’. Cannot insert duplicate key in object ‘dbo.Deneme’. The duplicate key value is (2).

Eğer identity Reseed işleminden haberiniz yoksa hemen aklınıza bir kaç seçenek gelir.
Ya tabloyu truncate edip data aktarımını yaparsınız, ya da tablodaki kayıt sayısı çoksa veya sorun yaşanan tablo sayısı fazla ise backup/restore işlemi ilk aklınıza gelecek şeylerdendir.
Ben karşılaştığım bu hata sonucunda Yazdığım bir script ile bütün tablolardaki identity değerini max identity + 1 olacak şekilde UPDATE ederek problemimi çözdüm.

Sizde de bu gibi bir durumla karşılaştığınızda bu scripti kullanarak probleminizi giderebilirsiniz.

-- CheckIdent işlemi yapacağımız tabloları tutmak için bir temp tablo create ettik
CREATE TABLE #MAX_ID_TABLE(TABLE_NAME NVARCHAR(500));
CREATE TABLE #x(c BIGINT, t NVARCHAR(520));

-- Identity Kolonu olan bütün tabloları isim ve identity değerlerini kaydettik
INSERT #MAX_ID_TABLE
SELECT TABLE_NAME,IDENT_CURRENT(TABLE_NAME) AS Current_Identity
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') = 1
AND TABLE_TYPE = 'BASE TABLE'

-- Cursor kullanarak bütün tabloların identity alanlarını güncelledik.
DECLARE @TableName varchar(100)
DECLARE @Current_Identity varchar(50)
DECLARE @cmd varchar(1000)

DECLARE CRSMAX_ID CURSOR FOR

SELECT TABLE_NAME FROM #MAX_ID_TABLE

OPEN CRSMAX_ID
FETCH NEXT FROM CRSMAX_ID INTO @TableName

WHILE @@FETCH_STATUS= 0
BEGIN
        DECLARE @MaxID int 
	DECLARE @cmd nvarchar(1000)
	SELECT  @cmd = 'INSERT #x SELECT  MAX(' + c.name +'), ''' +@TableName + ''' FROM ' + QUOTENAME(t.name) + ';'
	  FROM sys.tables AS t
	 INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id]
	 INNER JOIN sys.columns AS c ON t.[object_id] = c.[object_id]
	 WHERE c.is_identity = 1
	   and t.name = @TableName
	 order by t.object_id
	--print @cmd
	EXEC sp_executesql @Cmd
	
	SELECT  @cmd2 = 'DBCC CHECKIDENT ('''+@TableName+''', RESEED,'+cast((c + 1) as varchar(15))+');' FROM #x 
	EXEC sp_executesql @cmd2

FETCH NEXT FROM CRSMAX_ID INTO @TableName
END
CLOSE CRSMAX_ID
DEALLOCATE CRSMAX_ID

DROP TABLE #MAX_ID_TABLE;

Faydalı olması dileğiyle.

Leave a Reply