Delete all records from all tables of database

Question

Is there any way to delete all records from all tables of a database yet keeping all the constraints.

I used a script available on net but it fails where foreign keys are defined.

possible duplicate of How do you truncate all tables in a database using TSQL? — Blorgbeard
– Blorgbeard, Commented Apr 12, 2011 at 7:41
This answer will work with FK constraints: stackoverflow.com/questions/155246/… — Blorgbeard
– Blorgbeard, Commented Apr 12, 2011 at 7:42

Mikael Eriksson · Accepted Answer · 2011-05-26 12:54:03Z

2

CREATE PROCEDURE sp_EmplyAllTable
AS
EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
EXEC sp_MSForEachTable ‘DELETE FROM ?’
EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
GO

edited May 26, 2011 at 12:54

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

answered May 26, 2011 at 11:14

Praveen

362 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Delete all records from all tables of database

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related