MS SQL: how to truncate all tables without unlink the keys.

[Ersan Ercek]

Is it possible to truncate all tables without touching the constraints. All the best

[leckey 0]

By truncate do you mean delete records, but not the table itself? If there are constraints (for example table CUSTOMERS, which has records in SALES by a customer ID) you would have to delete the records from the child table (in this case SALES) first, then delete from the parent (in this case CUSTOMERS). I have had this issue at work where we want to delete something, so we have to start with the "smallest child" and work our way up. If it's something you plan to do frequently then just write a stored procedure for it so you can run when necessary.

Back in the blog beatch! http://CraptasticNation.blogspot.com/[^]

[Mycroft Holmes]

It can't be done simply, any table without an key can be truncated and use the following if there is a key

DELETE FROM TableName
DBCC CHECKIDENT (TableName, reseed, 1)

this will have the same effect as truncate. It will only work if there is no DATA based on the foriegn key, it will not violate the referential integrity of the data. I have a script that will clean out a database but it is hard coded for each application. It truncates all the non key table and then walks the key structure of the database manually. You could write a script/app that walked the FK structure of a database and created a hierarchy tree based on the keys (this is what code generators do)

Never underestimate the power of human stupidity RAH

[Ersan Ercek]

I want truncate because it resets primary key identity as well. Anyway I had some time to search on web and found a sp that drops the foreign keys, truncates the tables and re-creates them. It is very cleaverly written.

[Ersan Ercek]

I needed to reset identities as well so, deletion was not enough for me... But at the end I found a good sp that makes all I need.