How to reseed identities in all tables

Question

How to reseed identities in all tables

MillenniumAG

Mr or Mrs. 500

Points: 573
More actions
July 15, 2009 at 2:42 pm

#136526

Hi guys,
I want to create a script to loop in all tables to reseed the identities in a specific database
using:
DBCC CHECKIDENT(‘TableName’, RESEED, current_table_ident + 1)
How can i make a loop for this ?!!!
Thanks in advance!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ksullivan SSCrazy Points: 2346 More actions · Answer 1

Here's one method. Note the command is undocumented. Look around this site if you need more info on it. The NORESEED option is shown here to prevent someone from doing damage with a quick copy/paste/run.

Exec sp_MSForEachTable 'DBCC CHECKIDENT("?", NORESEED) '

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 2

ahmad.ghazi (7/15/2009)
Hi guys,
I want to create a script to loop in all tables to reseed the identities in a specific database
using:
DBCC CHECKIDENT(‘TableName’, RESEED, current_table_ident + 1)
How can i make a loop for this ?!!!
Thanks in advance!

Why would you want to do this? What would be the business reason behind doing such a thing?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MillenniumAG Mr or Mrs. 500 Points: 573 More actions · Answer 3

ok Jeff what i have is a peer to peer transactional replication on sql server 2005

and i need to adjust identity ranges for this because i have set the identity columns in the publisher to seed with 1 and increment by 2 then when i restore this database in the subscriber i want to change the seed to be 2 then i have to reseed the identity column in the publisher using the following command:

DBCC CHECKIDENT

(TableName,'reseed',HighestValueFoundInSubscriber+1)

and i have over 200 table and it's hard to make it manually

any other suggestion

Thanks

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 4

ahmad.ghazi (7/16/2009)
ok Jeff what i have is a peer to peer transactional replication on sql server 2005
and i need to adjust identity ranges for this because i have set the identity columns in the publisher to seed with 1 and increment by 2 then when i restore this database in the subscriber i want to change the seed to be 2 then i have to reseed the identity column in the publisher using the following command:
DBCC CHECKIDENT
(TableName,'reseed',HighestValueFoundInSubscriber+1)
and i have over 200 table and it's hard to make it manually
any other suggestion
Thanks

Thanks for taking the time to explain, Ahmad. I appreciate it. I'll have to sit down and figure out why you might have needed to go through all those gyrations with the increment by 2 because I don't currently understand why that was needed just to support replication.

In any case, the use of sp_MSForEachTable like KSullivan suggested would probably do the trick. If you need to include only replicated tables, you'll need to read which tables are being replicated from the system views (I think that can still be done in 2k5) or maintain a separate list and then step through that list. This is one of the few places where a loop could actually be condoned.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)