How to TRUNCATE Data From All TABLES of a DataBase in SQL Server

TRUNCATE Data From All TABLES of a DataBase in SQL Server

Here the following script help full to you to truncate data from all tables in a database

USE dbname
go
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
go
EXEC sp_MSforeachtable 'DELETE FROM ?'
go
EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'
go
EXEC sp_MSforeachtable 'DBCC CHECKIDENT ( ''?'', RESEED, 0)'
go

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

This is for for disable all the constraints in database

EXEC sp_MSforeachtable 'DELETE FROM ?'

Here we can delete all the data from tables

EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT

 Again we need to enable the constraints

Now,

 EXEC sp_MSforeachtable 'DBCC CHECKIDENT ( ''?'', RESEED, 0)'

By using DBCC command reset the identity

All process comes under truncation of a database 

The purpose of reseed is to re-set the identity value ,because truncate will automatically resets, but delete cannot. So it is mandatory to use the DBCC command for reset Identity value