SQL Server Reset Identity Increment for all tables

by

Do you have lots of tables which do not have a seed and increment of 1 ??

If not (by default, all tables have that), use this code:

exec sp_MSforeachtable @command1 = 'DBCC CHECKIDENT(''?'', RESEED, 1)'

MSforeachtable is an undocumented, but extremely handy stored proc which executes a given command against all tables in your database.

If you need to be absolutely exact, use this statement – it will generate a list of SQL statements to reseed all tables to their original SEED value:

SELECT 
    IDENT_SEED(TABLE_NAME) AS Seed,
    IDENT_INCR(TABLE_NAME) AS Increment,
    IDENT_CURRENT(TABLE_NAME) AS Current_Identity,
    TABLE_NAME,
    'DBCC CHECKIDENT(' + TABLE_NAME + ', RESEED, ' + CAST(IDENT_SEED(TABLE_NAME) AS VARCHAR(10)) + ')'
FROM 
    INFORMATION_SCHEMA.TABLES
WHERE 
    OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') = 1
    AND TABLE_TYPE = 'BASE TABLE'

Grab that last column in the output, and execute those statements and you’re done! 🙂

(inspired by a blog post by Pinal Dave)

Leave a Comment

tech