Delete,Truncate All Table's data from Database in SQL Server

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 command.
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.

Syntax:
 
Exec Sp_MSforeachtable

@Command1="Here your command without table name ?"

In 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 defined table

EXEC sp_MSforeachtable

@Command1 = "SELECT * FROM ?"

For Deleting all the data from all the tables:
EXEC sp_MSforeachtable

@Command1 = "Delete FROM ?"

For truncating all the data from all the tables:

EXEC sp_MSforeachtable

@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.

For Delete:

EXEC SP_MSFOREACHTABLE

--first disable all constraints

@command1 = "ALTER TABLE ? NOCHECK CONSTRAINT all",

--Delete all the data

@Command2="DELETE FROM ?",

-- enable all constraints after deletion

@command3="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

For Truncate:

EXEC SP_MSFOREACHTABLE

--first disable all constraints

@command1 = "ALTER TABLE ? NOCHECK CONSTRAINT All",

--Delete all the data

@Command2="TRUNCATE FROM ?",

-- enable all constraints after deletion

@command3="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT All"

For Drop all the table from the Database:

EXEC sp_MSforeachtable

@Command1 = "Drop Table ?"

EXEC sp_MSforeachtable

@Command1 = "Drop Table ? Print '? table dropped succefully'"
This commnad print all the table name which dropped.

Hope it will help to all :)