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
