SQL Server Reset Identity Increment For All Tables

In this tutorial i will let you know how to reset identity increment in SQL Server All Tables of a Database at once or you can say SQL Server Reset Identity Increment For All Tables.

We are doing this with the help of MSforeachtable stored procedure, which is a undocumented, but extremely handy stored proc which executes a given command against all tables in your database.

Command:

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

Or use it if you want to be specific and, in this last column will provide you query for the same.

SELECT 
    IDENT_SEED(TABLE_NAME) AS Seedvalue,
    IDENT_INCR(TABLE_NAME) AS Incrementvalue,
    IDENT_CURRENT(TABLE_NAME) AS CurrentIdentity,
    TABLE_NAME,
    'DBCC CHECKIDENT(' + TABLE_NAME + ', RESEED, ' + CAST(IDENT_SEED(TABLE_NAME) AS VARCHAR(10)) + ')' as query
FROM 
    INFORMATION_SCHEMA.TABLES
WHERE 
    OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') = 1
    AND TABLE_TYPE = 'BASE TABLE'