Resetting all identity columns in a database

Sometimes you just need to do it. Reset a load of a identity columns in a database.

So here is how to do it. First you need to find the identity columns

This a case of using one of the information schema views contained in INFORMATION_SCHEMA. The piece of SQL you need is

SELECT COLUMN_NAME,TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1 
ORDER BY TABLE_NAME 

This will give you back a list of all tables that have identity columns in the database. Now this can bring some unwanted tables as well, so it usually good to qualify the statement with a WHERE clause just to reduce the number of tables coming back. You can use the TABLE_SCHEMA(*) to sort out which schemas you want to adjust.

* Just be careful, sys.objects is the only real way to verify what schema the object belongs to.

So to reset the identity column you use the DBCC CHECKIDENT command in T-SQL and use the RESEED parameter. So to reset an identity column to 0 its

DBCC CHECKIDENT (‘TABLE_NAME’, RESEED, 0);

Now to put this all together and use a cursor to loop through the results from the first piece of code. Note we do not need the COLUMN_NAME in to update the identity column.

DECLARE @strSQL VARCHAR(1000)
DECLARE @Table VARCHAR(200)

DECLARE RowCounter CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1 
ORDER BY TABLE_NAME 

OPEN RowCounter 
FETCH NEXT FROM RowCounter 
INTO @Table 

WHILE @@FETCH_STATUS = 0 
BEGIN

SELECT @strSQL = 'DBCC CHECKIDENT(''' + @Table + ''',RESEED,0)'

PRINT @strSQL

EXEC(@STRSQL)

FETCH NEXT FROM RowCounter 
INTO @Table 
END

CLOSE RowCounter 
DEALLOCATE RowCounter 

This will reset all your identity columns to 0. Just be aware this is not something you want to do unless your table is empty. In some cases it may be applicable to reset the seed to a higher value than the highest value in the column for maintenance purposes.