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  

1 comment :

  1. Choose from the 1st 12 numbers, the 2nd 12 numbers or the 3rd 12 numbers. The earliest form of roulette is said to have been invented in a monastary in France in the 17th century. In an try to construct a machine that 코인카지노 may revolve endlessly and not using a|with no} want for energy from an outdoor supply, a physicist failed, but a roulette wheel was born.