Use Inbuilt Store Procedure for delete,drop,truncate all tables from one database

Hi Friends ,

Sometime we need to delete all data from all tables from one database ,
so instead of deleting all tables manually , Here is the solution

EXEC sp_MSforeachtable @command1 = "Delete ?"

You can use this Query in multiple ways depending on your requirement

for ex:

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

EXEC sp_MSforeachtable @command1 = "TRUNCATE TABLE ?"

As sp_MSforeachtable is Stored Procedure, that will execute for all the tables for database & @command1 is variable which will run against each table for connected database, now whatever you will write in the double quotes, that will be act as a command for each table, where '?' is the name of the table.

You won't be able to run TRUNCATE against all tables if you have foreign keys references

Here is one way to circumvent that

-- First disable 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

-- Now enable referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO

try this, it will clear your comcepts

EXEC sp_MSforeachtable @command1 = "SELECT * FROM ?" -- Selects all the rows form all the table

EXEC sp_MSforeachtable @command1 = "PRINT '?'" --Just print the tables names with owner(dbo)

It will delete all data from all tables from single database ,
so we will get blank database

Some time programmer test there application by inserting dummy data into tables
after finishing testing it will live on server so before getting live we can remove all dummy data from all table in one query

Hope it is helpfull for u

Thank You
Pradeep Deokar