Script -Deleting data of all tables of a Database in SQL

Script -Deleting data of all tables of a Database in SQL

The below Script will delete data from all tables of a Database.

Don't test the script in your Primary Database  although for data safety i have the script inside transaction block  and it will rollback all changes.
For tables having Referential Integrity truncate command will not work.So for that we are we are using Delete command.

--Tested and verified in SQL 2008 R2 and SQL2012

Begin tran

-- disable all referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

EXEC sp_MSForEachTable '
 IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
  DELETE FROM ?
 else
  TRUNCATE TABLE ?
'
GO
-- enable all  referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO
Rollback

Truncate will not Delete the Data if the table have referential Integrity(Foreign key) 
So for table having referential Integrity or foreign key Relation we are using Delete .
Test it by Removing the If condition that is deleting Data of all tables.

Begin tran

-- disable all referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'
GO
-- enable all  referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO
Rollback

Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'dbo.Employee' because it is being referenced by a FOREIGN KEY constraint.

Note :Truncate will reset the Identity column but Delete command doesn't  'So in order to Reset the Identity column for tables having Foreign key Relation ship in the above script
Use the below command


DBCC CHECKIDENT('TableName', RESEED, 0)--0 new reseed starting value