SQL SERVER – Reseting Identity Values for All Tables

SQL SERVER - Reseting Identity Values for All Tables headache 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 (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – Installing Data Quality Services (DQS) on SQL Server 2012
Next Post
SQL SERVER – DELETE, TRUNCATE and RESEED Identity

Related Posts

No results found.

20 Comments. Leave new

  • Sreelekha Vikram
    August 23, 2012 9:03 am

    Hi Pinal,

    Can we pass any type of SQL statement(i.e. DDL or DML) to this SP?

    Reply
  • I enjoyed the Conversation Pinal sir….

    Reply
  • Hammering….:)

    Reply
  • Here is an alternate method

    Reply
  • Ronald Kraijesteijn★ (@bonkiekonkie)
    August 23, 2012 6:13 pm

    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

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

    Reply
  • I had to laugh aloud after reading this conversation… Thanks for sharing Pinal!

    Reply
  • Ayman El-Ghazali
    August 23, 2012 9:52 pm

    Patience is definitely a virtue and the spam filter didn’t seem to help at all!

    Reply
  • 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

    Reply
  • Ronald Kraijesteijn★ (@bonkiekonkie)
    August 24, 2012 2:56 pm

    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

    Reply
  • 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.

    Reply
  • 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?

    Reply
  • 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.
    ???

    Reply
  • Suppose, I’ve to delete all the records except the very first record. In that case how can I reseed the values against maximum ids. Please keep in mind, by deleting one record, data is not deleted from only one table, it is deleted from multiple tables.

    Reply
  • I didn’t quite understand your question Shariq. Could you please provide a sample?

    Reply
  • Hi Pinal,
    sp_MSForEachTable is not working for Azure database. how can i do this on azure?

    Reply

Leave a Reply