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:
[youtube=http://www.youtube.com/watch?v=V6yRvhwUs9Y]Reference:Â Pinal Dave (https://blog.sqlauthority.com)
20 Comments. Leave new
Hi Pinal,
Can we pass any type of SQL statement(i.e. DDL or DML) to this SP?
Yes but note that it will be applied for all the tables unless you specified a specific table name
I enjoyed the Conversation Pinal sir….
Hammering….:)
Here is an alternate method
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
Hi,
Thanks for script! Sometimes problem with TRUNCATE is; when tables are related (or referenced), TRUNCATE fails. In such case DELETE is only option.
Or drop the constraints and recreate them afterwards.
That can also be scripted.
I had to laugh aloud after reading this conversation… Thanks for sharing Pinal!
Patience is definitely a virtue and the spam filter didn’t seem to help at all!
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
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
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.
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?
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.
???
Why does a gap in the identity column matter? You can use DBCC Checkident command to reset
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.
I didn’t quite understand your question Shariq. Could you please provide a sample?
Hi Pinal,
sp_MSForEachTable is not working for Azure database. how can i do this on azure?
Imran – then you need to do table by table.. manually.