Clear all the rows in a table resetting the identity specification back to zero and without affecting the foreign keys?

Question

We have already created the database framework, with all the relations and dependencies. But inside the tables were just the dummy data, and we need to get rid of these dummy data, and start adding the correct ones. How can we clear everything and leave the primary keys (IsIdentity: yes) back to zero, and also without affecting the foreign-table relational structure.

Thanks a lot!

score 10 · Accepted Answer · edited May 23 '17 at 12:33

You can take the following steps:

-- disable all foreign key constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- delete data in all tables
EXEC sp_MSForEachTable "DELETE FROM ?"

-- enable all constraints
exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

More on disabling constraints and triggers here

if some of the tables have identity columns we may want to reseed them

EXEC sp_MSforeachtable "DBCC CHECKIDENT ( '?', RESEED, 0)"

Note that the behaviour of RESEED differs between brand new table, and one which had had some date inserted previously from BOL:

DBCC CHECKIDENT ('table_name', RESEED, newReseedValue)

The current identity value is set to the newReseedValue. If no rows have been inserted to the table since it was created, the first row inserted after executing DBCC CHECKIDENT will use newReseedValue as the identity. Otherwise, the next row inserted will use newReseedValue + 1. If the value of newReseedValue is less than the maximum value in the identity column, error message 2627 will be generated on subsequent references to the table.

score 2 · Answer 2 · answered Apr 04 '11 at 15:41

2

I'd use TRUNCATE:

-- Truncate table (very fast, and it resets Identity as well)
EXEC sp_MSForEachTable "TRUNCATE TABLE ?"

Of course, disable and re-enable check constraints too, as suggested by kristof.

answered Apr 04 '11 at 15:41

Diego

7,312
5
31
38

Does Truncate reset Identity as well? – Zapnologica Oct 03 '14 at 07:29
1

As I wrote in the original answer, yes (the `DELETE` command, instead, does not). – Diego Oct 03 '14 at 08:18

score 1 · Answer 3 · answered Dec 23 '09 at 09:58

1

Reseeding:

DBCC CHECKIDENT (yourtable, reseed, 1)

is for setting the primary key on 1

delete from table should delete the data, but not affect any other things.

answered Dec 23 '09 at 09:58

pipelinecache

3,954
1
17
16

*The DELETE statement conflicted with the REFERENCE constraint "FK_BlahAbcs_AnotherBlah".* How can we avoid this? – Jronny Dec 23 '09 at 10:02
disable temporarely the constraint like Christof did. – pipelinecache Dec 23 '09 at 10:46

this. __curious_geek · Answer 4 · 2009-12-23T10:14:41.893

1

Generate Database schema-only sql file using Database publishing wizard and enable the option to drop the tables if exists. Run this script on your database and This will flush everything and give you fresh schema as you need.

edited Dec 23 '09 at 10:14

answered Dec 23 '09 at 09:59

this. __curious_geek

42,787
22
113
137

Sounds promising... But how or where can we find the publishing wizard? =) – Jronny Dec 23 '09 at 10:04
Here - http://www.microsoft.com/downloads/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en – this. __curious_geek Dec 23 '09 at 10:14
Explore unconventional ways for more promising results! – this. __curious_geek Dec 24 '09 at 08:03

Clear all the rows in a table resetting the identity specification back to zero and without affecting the foreign keys?

4 Answers4

Linked