SQL SERVER – Reseting Identity Values for All Tables

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.

Watch the video on the same subject:

Reference: Pinal Dave (http://blog.sqlauthority.com)

21 thoughts on “SQL SERVER – Reseting Identity Values for All Tables

  1. This is also a nice script to generate SQL statements. Set output of the SQL to “Text”

    SELECT ‘
    GO
    DBCC CHECKIDENT(”ps.’ + TabelNaam + ”’,RESEED,0)’

    FROM
    — LT: Linked Tables
    (SELECT O.name AS TabelNaam
    , S.name AS SchemaName
    FROM sysobjects AS O
    LEFT OUTER JOIN sys.schemas AS S ON O.uid = S.schema_id
    WHERE O.type = ‘u’ AND O.uid=5
    AND CHARINDEX(‘_’,O.name)=0
    –ORDER BY O.[name]
    ) AS LT
    WHERE SchemaName = ‘dbo’
    ORDER BY TabelNaam

    Like

  2. Hi,
    Thanks for script! Sometimes problem with TRUNCATE is; when tables are related (or referenced), TRUNCATE fails. In such case DELETE is only option.

    Like

  3. Hi

    Truncate will not be allowed if the user has any referential integrity in place on the tables to be truncated. In this case you would need to disable any integrity before truncating and even loading the tables

    Regards Perry

    Like

  4. Pingback: SQL SERVER – DELETE, TRUNCATE and RESEED Identity « SQL Server Journey with SQL Authority

  5. Hi Perry,

    You can also create DELETE statements and run that script a couple of times till all tables are empty:

    SELECT ‘
    GO
    DELETE FROM ps.’ + TabelNaam

    FROM
    — LT: Linked Tables
    (SELECT O.name AS TabelNaam
    , S.name AS SchemaName
    FROM sysobjects AS O
    LEFT OUTER JOIN sys.schemas AS S ON O.uid = S.schema_id
    WHERE O.type = ‘u’ AND O.uid=5
    –AND CHARINDEX(‘_’,O.name)=0
    –ORDER BY O.[name]
    ) AS LT
    WHERE SchemaName = ‘ps’
    ORDER BY TabelNaam

    After that, you can reset the ident fields with the script above.

    Note: set the output to Text

    Like

  6. Pingback: SQL SERVER – Answer – Value of Identity Column after TRUNCATE command « SQL Server Journey with SQL Authority

  7. what we have to do when any one uses the identity column as primary key, and referenced to other table(foreign key) then how can we re-generate the Identity column, without changing the relationship of columns as well as data.

    Like

  8. Hi i have used following query to delete the data from all tables in my db.

    CREATE PROCEDURE [dbo].[usp_DeleteAllData]
    AS
    EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
    EXEC sp_MSForEachTable ‘ALTER TABLE ? DISABLE TRIGGER ALL’
    EXEC sp_MSForEachTable ‘DELETE FROM ?’
    EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
    EXEC sp_MSForEachTable ‘ALTER TABLE ? ENABLE TRIGGER ALL’
    EXEC sp_MSFOREACHTABLE ‘SELECT * FROM ?’
    GO

    on execution of the above sp am able to delete the data. Can use truncate instead of ‘Delete’ so that it will automatically reseed the identities or shall i include the query u provided at the end of the sp?

    Like

  9. Hi, I want to ask..
    how do I use an identity increment again after i delete it without deleting the other rows?
    For example, the last identity is 151, and i added 152, but i delete the 152,…
    so since the last row still 151, how to make it become 152 again not 153, when i add a new row again.
    ???

    Like

  10. Pingback: SQL SERVER – RESEED Identity Column in Database Table – Rest Table Identity Value – SQL in Sixty Seconds #051 | SQL Server Journey with SQL Authority

  11. Pingback: SQL SERVER – Weekly Series – Memory Lane – #043 | Journey to SQL Authority with Pinal Dave

  12. Pingback: SQL SERVER – Weekly Series – Memory Lane – #044 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s