Delete All Rows From All Tables in SQL Server

In my recent project my task was to clean an entire database by using sql statement and each table having many constraints like Primary Key and Foreign Key. There are more than 1000 tables in database so its not possible to write a delete query on each and ever table. 

I have found a solution to delete all table data within a database. In this article I will explain a safe and easy way to delete all of the records from all of the tables in a SQL Server Database:-

By using a stored procedure named sp_MSForEachTable which allows us to easily process some code against each and every table in a single database. It means that it is used to process a single T-SQL command or a different T-SQL commands against every table in the database.

So follow the below steps to truncate all tables in a SQL Server Database:-

Step 1- Disable all constraints on the database by using below sql query :-

EXEC sys.sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 

Step 2- Execute a Delete or truncate operation on each table of the database by using below sql command :-

EXEC sys.sp_msforeachtable 'DELETE FROM ?'  

Step 3- Enable all constraints on the database by using below sql statement

EXEC sys.sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

delete all rows from table sql server 2012

I hope you will enjoy the SQL Server tips while while writing SQL Query. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome. Also If you like this article, don't forget to share this article with your friends and colleagues.