SQL Server: How to delete/drop/truncate all the tables from Database

To delete/drop/truncate all the tables from Database, you can use the following SQL commads to perform the desired function

EXEC sp_MSforeachtable @command1 = "DELETE FROM ?"
EXEC sp_MSforeachtable @command1 = "TRUNCATE TABLE ?"

Explanation

sp_MSforeachtable is a hidden Stored Procedure in sql server, that will execute for all the tables for database (no rollback)
@command1 is variable which will run against each table for connected database.
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.

Word of caution. Make sure execute these commands on test Database before actually executing in the desired database

/Adnan




Note: This post is from my old blog dated Thursday, February 2011, that unfortunately no longer exist