In this blog I have
discussed about how to Drop all the user defined table from the database OR Select/Delete/Truncate
data from all the database tables OR alter the entire database table in one
In SQL Server there is a system defined Procedure to perform Select, Delete,
Truncate, Drop, Alter command on the entire database table in one shot command,
the name of the procedure is: Sp_MSforeachtable this procedure takes 7
parameter in this 2 parameters are mendotarory rest 5 are optional. If you want to know how
this procedure works just run following Query on the query analyzer
SP_Helptext Sp_MSforeachtable, this
command give you all the Procedure implementation. You cannot rollback data after
using this Store procedure.
@Command1="Here your command without
table name ?"
this "Your command without table name" is the query without table
name which you want to perform the entire database tables.
"?" Is the name of table which is replaced automatically with table name
(This logic is written in the Store procedure);
For selecting data from the all
user defined tables:
-- Selects all the rows form all the database’s user
@Command1 = "SELECT * FROM ?"
For Deleting all the data from all the
@Command1 = "Delete FROM ?"
For truncating all the data from all the
@Command1 = "Truncate Table ?"
Above command will work fine, if the table did not have any foreign key
refrence.if there is any refrences then it will not work.
in that case you need to disable all the constraints before deleting,and after
delete you need to enable all the constraints.
@command1 = "ALTER TABLE ? NOCHECK CONSTRAINT all",
--Delete all the
@Command2="DELETE FROM ?",
-- enable all
constraints after deletion
TABLE ? WITH CHECK CHECK CONSTRAINT all"
@command1 = "ALTER TABLE ? NOCHECK CONSTRAINT All",
@Command2="TRUNCATE FROM ?",
TABLE ? WITH CHECK CHECK CONSTRAINT All"
For Drop all the table from the Database:
@Command1 = "Drop Table ?"
"Drop Table ? Print '? table dropped succefully'"
This commnad print all the table name which dropped.
Hope it will help to all :)