delete all rows from all tables

Delete all rows from all tables in database

Have you ever come across the problem how to delete all rows from all tables in a sql server database? Our database has more than 500 tables so it was not possible to write a delete query on each and every table and each table having many constraints like primary key and foreign key. So manually writing a query is a very tedious job.

The solution for MS SQL Delete all rows from all tables

I have found two ways to delete all the table data in a database. In this blog post, I will explain each way one by one. Follow below steps with t-SQL delete all rows from all tables:

  • Using Undocumented Stored Procedure sp_MSForEach Table: The Stored Procedure “sp_MSforeachtable” 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 number of different T-SQL commands against every table in the database.

Using the following steps we can delete all rows from all tables in a sql server database.

  • Step 1 – Disable all constraints first:-
  • Step 2 – Execute a Delete or truncate operation on each table of the database:-
  • Step 3 – Enable all constraints on the database:-

sql query to delete all rows from all tables delete all rows from all tables Delete all rows from all tables in a SQL Server Database sql query

What do you think?

I hope you liked this article on how to delete all rows from all tables in a sql server database. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.

LEAVE A REPLY

Please enter your comment!
Please enter your name here