Tuesday 9 September 2014

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 ?"


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


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

About the Author

Adnan Zameer, Lead Developer at Optimizley UK, is a certified Microsoft professional, specializing in web app architecture. His expertise includes Optimizley CMS and Azure, showcasing proficiency in crafting robust and efficient solutions.


Post a Comment