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:

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.