Hi.,

Here in this post i would like to explain., how to delete data form the data base table using a single query. and also resetting the auto increment property. that means it will set to stating value.



By using " DELETE FROM <TABLE NAME> " Command we can delete data from only one table but the auto increment property will not set to starting point.

Using TRUNCATE FROM <TABLE NAME> query also we can delete the data as well as we can reset auto increment property to starting point. But it will rise an arror if the table has constraints.

So using these commands we can delete all the data from the all tables at a time., with out loosing relations and views and store procedure. For this purpose here i am giving a small query which will fulfill our requirement.,

-- to swithch off the cnstraints untill data will be deleted
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
-- To Delete the data from all the table in the data base and resetting the auto increment property
EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
begin
DELETE FROM ?
DBCC CHECKIDENT ( ''?'' , RESEED, 0)
end
else
TRUNCATE TABLE ?
'
GO

-- To switch on the cnstraints
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO

I hope this is very use full to you all people.

Please suggest me., and leave a comment as a opinion or a query.
Enjoy the flavor.