views:

151

answers:

3

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!

+1  A: 

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.

pipelinecache
*The DELETE statement conflicted with the REFERENCE constraint "FK_BlahAbcs_AnotherBlah".* How can we avoid this?
Jronny
disable temporarely the constraint like Christof did.
pipelinecache
+1  A: 

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.

this. __curious_geek
Sounds promising... But how or where can we find the publishing wizard? =)
Jronny
this. __curious_geek
Explore unconventional ways for more promising results!
this. __curious_geek
+1  A: 

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.

kristof
works perfectly as I needed. Thanks
Jronny