Tuesday 9 September 2014

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

        , , , ,       1 comment    
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  

1 comment :

  1. Anonymous30 November 2022 at 15:29

    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.

    ReplyDelete