Script to delete all data from Sql Server database

Sometimes while developing we have situations when  earlier fill some dummy data in our database in earlier stages of development using some data generation tools such as RedGate Sql Data Generator or EMS Data Generator.

This approach is good when we are in initial stages of kick off stages of a project, also after sometime we want to check some related and sensible data so we want to fill the data ourselves, thus now we want to delete all old data from the databas.

Another situation where this can be helpful is when our client wants to clear all his database for previous years and start a new or a fresh copy(This is a rare case but it’s sometimes the demand of business)

Given below is the script which will let you delete all the records in the database and also preserve your referential integrity and also can reseed each table to their initial values.

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

EXEC sp_MSForEachTable '
 IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
  DELETE FROM ?
 else 
  TRUNCATE TABLE ?
'
GO

This piece of script disables the referential integrity and deletes the data in all the tables.

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO

This script again enables the referential integrity on all the tables.

EXEC sp_MSForEachTable ' 
IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1 
DBCC CHECKIDENT (''?'', RESEED, 0) 
' 
GO

This script reset all the seed to their initial values.If you don’t want to reseed your tables to initial values just skip this script.

Given below is the code put together for your convenience to use.

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

EXEC sp_MSForEachTable '
 IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
  DELETE FROM ?
 else 
  TRUNCATE TABLE ?
'
GO


EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO


EXEC sp_MSForEachTable ' 
IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1 
DBCC CHECKIDENT (''?'', RESEED, 0) 
' 
GO

“If someone wants to purchase Sql Server 2008 at a discounted price, you can purchase at a low price from eCostSoftware.”