Use SQL statements to reset identity columns (self-increment) for all tables in the database

can be applied to 2 different scenarios:

1. Clear all tables of data, after the data is emptied, it is best to be able to make the table identity column starting from 1 to count, so to reset the identity column's current value.

2. With replication, after the subscription synchronization data, the Subscriber data does not automatically grow, such as the self-increment ID of the 9527, but if the middle of the skipped ID, will automatically populate the missing passing ID, the execution of the following code, you can start from the 9527 should grow.

Declare @tablename varchar( -) Declare @sql varchar( +)DeclareCurcursor  for SelectName fromsys.tablesOpencurFetch Next  fromCur into @tablename while @ @fetch_status=0beginSet @sql='if (select COUNT (1) from'+@tablename+') <=0 and exists (SELECT * from Sys.columns where Is_identity=1 and object_id=object_id (" "+@tablename+" ")) begin--dbcc checkident ('+@tablename+', reseed,1) DBCC CHECKIDENT ('+@tablename+', reseed) End'exec(@sql)Fetch Next  fromCur into @tablename End ClosecurdeallocateCur

Tested, no problem.

--a table name with an identity column (self-increment ID) has been tested for a problem-free buildDeclare @tablename varchar( -) Declare @sql varchar( +)Declare @objiecid intDeclareCurcursor  for Select object_id  fromSys.columnswhereIs_identity=1  OpencurFetch Next  fromCur into @objiecid while @ @fetch_status=0beginSelect @tablename=Name fromSys.tableswhere object_id=@objiecid--Print @tablenamePrint 'DBCC CHECKIDENT (" "+@tablename+" ", Reseed)'Fetch Next  fromCur into @objiecid End ClosecurdeallocateCur

Use SQL statements to reset identity columns (self-increment) for all tables in the database