Sql-Server
恢復所有 IDENTITY 種子(數據庫恢復後不同步)
我們最近從備份(模式和數據)對數據庫進行了完全恢復。
嘗試插入新行時,我們收到“無法在對像中插入重複鍵”錯誤消息,它試圖將重複鍵插入標識列。我們沒有手動指定這些列的值。
我們可以通過執行來解決特定表的問題
DBCC CHECKIDENT ('Table', RESEED, 10);
(將 10 更改為下一個可用的標識值)
但是,此問題似乎會影響整個數據庫中的每個表。(我們在每個表上使用身份 PK 列)
你知道嗎
a) 最初是什麼原因導致這種情況發生?
b) 如果有一種簡單的方法可以重新設定所有表的種子以使用下一個可用的標識值。
下一個腳本為伺服器中的每個表生成一個 DBCC CHECKIDENT 命令,將目前 IDENT_CURRENT 值加 1。
注意我評論過:
--EXEC (@SCRIPT);
在 CURSOR 中,我強烈建議您在執行之前檢查每個生成的命令。
此外,您可以在 SELECT 語句中取消註釋
--AND T.TABLE_CATALOG = 'YOUR_CATALOG'
只是為一個目錄執行它。
您可以獲取文本形式的結果,並從 SSMS 執行命令,而不是執行每個命令。
CREATE TABLE T1 (ID INT IDENTITY, FOO INT); CREATE TABLE T2 (NID INT IDENTITY, BAR INT); INSERT INTO T1 VALUES (1),(2),(3); INSERT INTO T2 VALUES (1),(2),(3),(4),(5); GO
受影響的 8 行
DECLARE @TABLE_CATALOG NVARCHAR(128), @TABLE_SCHEMA NVARCHAR(128), @TABLE_NAME NVARCHAR(128), @COLUMN_NAME NVARCHAR(128); DECLARE @SCRIPT NVARCHAR(MAX); SET @SCRIPT = ''; BEGIN TRY CREATE TABLE #SCRIPTS ( COMMANDS NVARCHAR(500) ); DECLARE CURIDENT CURSOR READ_ONLY LOCAL FAST_FORWARD FOR SELECT C.TABLE_CATALOG, C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS C JOIN INFORMATION_SCHEMA.TABLES T ON C.TABLE_CATALOG = T.TABLE_CATALOG AND C.TABLE_SCHEMA = T.TABLE_SCHEMA AND C.TABLE_NAME = T.TABLE_NAME WHERE TABLE_TYPE = 'BASE TABLE' AND COLUMNPROPERTY(OBJECT_ID(C.TABLE_SCHEMA + '.' + C.TABLE_NAME), C.COLUMN_NAME, 'IsIdentity') = 1 --AND T.TABLE_CATALOG = 'YOUR_CATALOG' ORDER BY C.TABLE_NAME; OPEN CURIDENT; FETCH NEXT FROM CURIDENT INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME; WHILE @@FETCH_STATUS = 0 BEGIN SET @SCRIPT = N'DBCC CHECKIDENT(''' + (@TABLE_SCHEMA + N'.' + @TABLE_NAME) + N''', RESEED, ' + CAST(COALESCE(IDENT_CURRENT(@TABLE_SCHEMA + N'.' + @TABLE_NAME) + 1, 0) AS VARCHAR(30)) + ');' INSERT INTO #SCRIPTS VALUES (@SCRIPT); --UNCOMENT TO EXECUTE FOR EACH ROW --EXEC (@SCRIPT); FETCH NEXT FROM CURIDENT INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME; END CLOSE CURIDENT; DEALLOCATE CURIDENT; SELECT * FROM #SCRIPTS; IF OBJECT_ID('tempdb..#SCRIPTS') IS NOT NULL DROP TABLE #SCRIPTS; END TRY BEGIN CATCH IF CURSOR_STATUS('LOCAL','CURIDENT') > -1 BEGIN CLOSE CURIDENT; DEALLOCATE CURIDENT; END IF OBJECT_ID('tempdb..#SCRIPTS') IS NOT NULL DROP TABLE #SCRIPTS; END CATCH GO
| 命令 | | :------------------------------------ | | DBCC CHECKIDENT('dbo.T1', RESEED, 4); | | DBCC CHECKIDENT('dbo.T2', RESEED, 6); |
dbfiddle在這裡
如果不想執行整個腳本,可以通過執行下一個查詢得到相同的結果:
SELECT N'DBCC CHECKIDENT(''' + (C.TABLE_SCHEMA + N'.' + C.TABLE_NAME) + N''', RESEED, ' + CAST(COALESCE(IDENT_CURRENT(C.TABLE_SCHEMA + N'.' + C.TABLE_NAME) + 1, 0) AS NVARCHAR(30)) + ');' FROM INFORMATION_SCHEMA.COLUMNS C JOIN INFORMATION_SCHEMA.TABLES T ON C.TABLE_CATALOG = T.TABLE_CATALOG AND C.TABLE_SCHEMA = T.TABLE_SCHEMA AND C.TABLE_NAME = T.TABLE_NAME WHERE TABLE_TYPE = 'BASE TABLE' AND COLUMNPROPERTY(OBJECT_ID(C.TABLE_SCHEMA + '.' + C.TABLE_NAME), C.COLUMN_NAME, 'IsIdentity') = 1 --AND T.TABLE_CATALOG = 'YOUR_CATALOG' ORDER BY C.TABLE_NAME; GO
| (無列名) | | :------------------------------------ | | DBCC CHECKIDENT('dbo.T1', RESEED, 4); | | DBCC CHECKIDENT('dbo.T2', RESEED, 6); |
dbfiddle在這裡