0

So here is the normal code if I want to reset all tables to 1 

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

what if i want to reset it to the max ID number that is there in my Table i mean something like that 

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

anyone can explain why it doesn't work ? thanks

the purpose of doing this is , sometimes when the server shuts down , the identity column just jumps from its value by 1000 for example if the current identity value is 2 and the server shuts down , next value would be 1002

Willy
  • 3,506
  • 3
  • 22
  • 35
  • 2
    Yes, it does that. Assuming you actually *care about the value* (and in most cases you probably should not) you can disable this behaviour; http://stackoverflow.com/questions/14146148/identity-increment-is-jumping-in-sql-server-database – Alex K. Nov 26 '14 at 16:40
  • I would not recommend just blindly reseeding like this. Why do you care what the next value is or if there are gaps in an identity. This is normal and expected behavior. – Sean Lange Nov 26 '14 at 16:51
  • Ah thanks guys for your response, I added the -T272 to the startup parameter , the reason that makes me care and worry too much about these jumps , is that the frequency of Power Failures we have here in Egypt is too high , I mean like sometimes power would cut off for 2 to 3 times a day so basically the ID would jump 3000 if it happened , I mean one day I would run out of Integer memory , and if I changed it to big int , It would jump by 10000 . – Willy Nov 30 '14 at 12:11

1 Answers1

0

This works for me:

exec sp_MSforeachtable @command1 =
'declare @contar int = (select count(*) from ?)
DBCC CHECKIDENT(''?'', RESEED, @contar)'
user11334971
  • 1