Delete Records from multiple tables

2009-12-17T06:52:00.002+00:00

In many cases you may want to delete All the records from multiple tables . form
example you have generated tast data with

SQL Data Generator and know you want to delete all the records in tabels at once.

for this goal you can use this code :

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL';

EXEC sp_msforeachtable 'DELETE FROM ?';

EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL';

you my ask that way you haven't use "Truncate Table" insted of "Delete From" !!!

the answer is in some cases you may have foregn key and "Truncate Table" doesn't
work in this case. But if you don't have any FK constraints you can use this code
insted that is more faster and better :

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL';

EXEC sp_msforeachtable 'TRUNCATE TABLE ?';

EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL';

Happy new Blog

2009-09-27T14:27:00.001+01:00

I'm glad to say that this is my new blog and it's my first post ...

MRP's Blog

Delete Records from multiple tables

Happy new Blog