SQL: delete all data from all tables ignoring constraints

sql-empty-entire-database-ignore-constraints.sql

-- Step 1: Disable all foreign key constraints
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

-- Step 2: Generate delete statements for all tables
-- This part will dynamically generate the delete statements
DECLARE @sql NVARCHAR(MAX) = N'';

-- Collect all table names in the current database
SELECT @sql += 'DELETE FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + '; '
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.type = 'U' -- 'U' for user-defined tables

-- Execute the generated delete statements
EXEC sp_executesql @sql;

-- Step 3: Re-enable all foreign key constraints
EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'

-- Step 4 (Optional): Verify that all constraints are re-enabled
-- This step is to ensure that all constraints are back to their original state
DECLARE @verifySql NVARCHAR(MAX) = N'';
SELECT @verifySql += 'ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' WITH CHECK CHECK CONSTRAINT ALL; '
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.type = 'U' -- 'U' for user-defined tables

EXEC sp_executesql @verifySql;