Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save cemerson/6630e69acd2e417f88586dfdead1da9c to your computer and use it in GitHub Desktop.
Save cemerson/6630e69acd2e417f88586dfdead1da9c to your computer and use it in GitHub Desktop.
SQL: delete all data from all tables ignoring constraints
-- 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment