Deleting all Records from all Tables having Foreign Keys in SQL Server

In SQL Server, deleting all records from all tables having foreign keys can be a complex task, especially when dealing with multiple tables and relationships. Here's a step-by-step approach to achieve this.

Step 1: Disable all Foreign Key Constraints

First, we need to disable all foreign key constraints to avoid any errors during the deletion process.

EXEC sp_msforeachtable `ALTER TABLE ? NOCHECK CONSTRAINT all`

Step 2: Delete Records from all Tables

Next, we'll delete records from all tables using a cursor to iterate through each table.

DECLARE @sql NVARCHAR(MAX) = '' SELECT @sql += 'DELETE FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + '; ' FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id EXEC sp_executesql @sql

Step 3: Enable all Foreign Key Constraints

Finally, we'll enable all foreign key constraints again.

EXEC sp_msforeachtable `ALTER TABLE ? CHECK CONSTRAINT all`

Example:

Let's say we have a database with multiple tables, including Customers, Orders, and OrderDetails, with foreign key relationships between them.

Table Structure:

Table Name Columns
Customers CustomerID, Name, Address
Orders OrderID, CustomerID, OrderDate
OrderDetails OrderDetailID, OrderID, ProductID, Quantity

Foreign Key Relationships:

  • Orders.CustomerID references Customers.CustomerID
  • OrderDetails.OrderID references Orders.OrderID

Deleting all Records:

EXEC sp_msforeachtable `ALTER TABLE ? NOCHECK CONSTRAINT all` DECLARE @sql NVARCHAR(MAX) = '' SELECT @sql += 'DELETE FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + '; ' FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id EXEC sp_executesql @sql EXEC sp_msforeachtable `ALTER TABLE ? CHECK CONSTRAINT all`

After executing these steps, all records from all tables having foreign keys will be deleted.