Reseed SQL Tables

2017-09-15
 |  No Comments

This one is well and truly documented all over the web. I’ve put it here just so that I have a quick copy paste code instead of running all over the place.

I found that in some cases the normal command DBCC CHECKIDENT (‘tablename’, RESEED) didn’t reset to the max. (Usually when the current identity value was larger than the max). So to get around that I run it twice. One to set the value to zero, and then again to set it to the max.

 

--To reseed all tables after a sql restart
EXEC sp_MSForEachTable '
 IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1
 BEGIN
	 DBCC CHECKIDENT (''?'', RESEED, 0)
 END'
GO
EXEC sp_MSForEachTable '
 IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1
 BEGIN
	PRINT ''RESEEDING TABLE - ?''
	DBCC CHECKIDENT (''?'', RESEED)
 END'
GO
Tags: ,