SQL SERVER – Reseting Identity Values for All Tables
- by pinaldave
Sometime email requesting help generates more questions than the motivation to answer them. Let us go over one of the such examples. I have converted the complete email conversation to chat format for easy consumption. I almost got a headache after around 20 email exchange. I am sure if you can read it and feel my pain.
DBA: “I deleted all of the data from my database and now it contains table structure only. However, when I tried to insert new data in my tables I noticed that my identity values starts from the same number where they actually were before I deleted the data.”
Pinal: “How did you delete the data?”
DBA: “Running Delete in Loop?”
Pinal: “What was the need of such need?”
DBA: “It was my development server and I needed to repopulate the database.”
Pinal: “Oh so why did not you use TRUNCATE which would have reset the identity of your table to the original value when the data got deleted? This will work only if you want your database to reset to the original value. If you want to set any other value this may not work.”
DBA: (silence for 2 days)
DBA: “I did not realize it. Meanwhile I regenerated every table’s schema and dropped the table and re-created it.”
Pinal: “Oh no, that would be extremely long and incorrect way. Very bad solution.”
DBA: “I understand, should I just take backup of the database before I insert the data and when I need, I can use the original backup to restore the database. This way I will have identity beginning with 1.”
Pinal: “This going totally downhill. It is wrong to do so on multiple levels. Did you even read my earlier email about TRUNCATE.”
DBA: “Yeah. I found it in spam folder.”
Pinal: (I decided to stay silent)
DBA: (After 2 days) “Can you provide me script to reseed identity for all of my tables to value 1 without asking further question.”
Pinal:
USE DATABASE;
EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1
DBCC CHECKIDENT (''?'', RESEED, 1)'
GO
Our conversation ended here. If you have directly jumped to this statement, I encourage you to read the conversation one time. There is difference between reseeding identity value to 1 and reseeding it to original value – I will write an another blog post on this subject in future.
Reference: Pinal Dave (http://blog.sqlauthority.com)
Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology
