Delete all rows from all tables in a SQL Server Database

806
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