SQL Server Queries - Delete all records from all tables having foreign keys

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.