2

Is there anyway 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.

Please provide step by step illustration as I'm new to databses.

Thanks!

Saubhagya
  • 1,075
  • 1
  • 9
  • 12
  • possible duplicate of [How do you truncate all tables in a database using TSQL?](http://stackoverflow.com/questions/155246/how-do-you-truncate-all-tables-in-a-database-using-tsql) – Blorgbeard Apr 12 '11 at 07:41
  • 1
    This answer will work with FK constraints: http://stackoverflow.com/questions/155246/how-do-you-truncate-all-tables-in-a-database-using-tsql/156813#156813 – Blorgbeard Apr 12 '11 at 07:42

1 Answers1

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
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
Praveen
  • 36
  • 2