恢復所有 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在這裡

引用自：https://dba.stackexchange.com/questions/198827