Restore all IDENTITY seeds (out of sync since database restore) We have recently done a full restore on a database from a backup (schema &amp; data).

When trying to insert new rows we are getting the "Cannot insert duplicate key in object" error message, it is trying to insert duplicate keys into an identity column. We are not manually specifying the values for those columns.

We can fix the issue for a specific table by running

DBCC CHECKIDENT ('Table', RESEED, 10);


(changing 10 to be the next available identity value)

However, this issue appears to affect every single table on the whole database. (We use identity PK columns on every table)

Do you know

a) What might have caused this to happen in the first place?

b) If there is an easy way to reseed all tables to use the next available identity value.
Next script generates a DBCC CHECKIDENT command for each table in your server adding 1 to the current IDENT_CURRENT value.

Notice I've commented:

--EXEC (@SCRIPT);


within the CURSOR, I strongly suggest you to check each generated COMMAND before to execute it.

Additionally you can un-comment within the SELECT statement

--AND      T.TABLE_CATALOG = 'YOUR_CATALOG'


just to execute it for one catalog.

Instead of execute each command, you can get results as text, and execute commands from 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 rows affected






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') &gt; -1
    BEGIN
        CLOSE CURIDENT;
        DEALLOCATE CURIDENT;
    END

    IF OBJECT_ID('tempdb..#SCRIPTS') IS NOT NULL
        DROP TABLE #SCRIPTS;

END CATCH


GO



| COMMANDS                              |
| :------------------------------------ |
| DBCC CHECKIDENT('dbo.T1', RESEED, 4); |
| DBCC CHECKIDENT('dbo.T2', RESEED, 6); |



dbfiddle here

If don't want to execute the whole script, you can get the same result by executing next query:




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



| (No column name)                      |
| :------------------------------------ |
| DBCC CHECKIDENT('dbo.T1', RESEED, 4); |
| DBCC CHECKIDENT('dbo.T2', RESEED, 6); |



dbfiddle here