SQL Server, Empty your Database & Reset Identity Columns **made Simple

Every so often a database needs to go back to a clean slate: all data gone, schema intact, identity columns back to their seeds — and, in the era of SSMS database diagrams, those diagrams preserved too. This is the script I wrote for exactly that in 2009. Sixteen years later it still runs unmodified on SQL Server 2022 and Azure SQL, which says something about how stable this corner of T-SQL is.

The engine underneath is sp_MSforeachtable, an undocumented but widely used system procedure that executes a command once per user table, substituting ? with the full table name. Being undocumented, it comes with no support guarantees — but it has shipped with every SQL Server release since 6.5 era code and remains the shortest path to “run this against everything”.

The core script

-- Disable constraints & triggers
EXEC sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL';
EXEC sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL';

-- Delete everything
EXEC sp_MSforeachtable 'DELETE ?';

-- Re-enable constraints & triggers
EXEC sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL';
EXEC sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL';

-- Reseed identity columns on tables that have them
EXEC sp_MSforeachtable 'IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1
    DBCC CHECKIDENT (''?'', RESEED, 0);';

Why each step looks the way it does

NOCHECK before DELETE. With foreign keys in place, delete order would matter; with them disabled, any order works. Re-enabling with CHECK CONSTRAINT ALL just flips the switch back on — it does not re-validate existing data (that would be WITH CHECK CHECK CONSTRAINT). Since the tables are empty at that point, there is nothing to validate anyway.

DELETE, not TRUNCATE. TRUNCATE TABLE is faster and resets identity on its own, but it fails on any table referenced by a foreign key — even a disabled one. For a loop that touches every table, DELETE is the only option that always succeeds.

The RESEED gotcha. DBCC CHECKIDENT (‘?’, RESEED, 0) sets the current identity value to 0. For a table that had rows before the DELETE — which is the normal case here — the next insert gets 0 + 1 = 1. But on a table created and never inserted into, the first row after reseeding takes the seed value itself, 0. If your seeds start at 1 and you care about the difference, spot-check empty tables after the run.

The OBJECTPROPERTY guard skips tables without an identity column, which would otherwise throw an error from CHECKIDENT.

Keeping your database diagrams alive

sysdiagrams is an ordinary user table, so the DELETE wipes it along with everything else. The trick below is the full version of the script: stash the diagrams in a temp table first, then restore them after the purge.

EXEC sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL';
EXEC sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL';

-- Stash the diagrams; sysdiagrams is an ordinary user table
CREATE TABLE #tmpDiagrams (
    [name]         [sysname]        NOT NULL,
    [principal_id] [int]            NOT NULL,
    [diagram_id]   [int]            NOT NULL,
    [version]      [int]            NULL,
    [definition]   [varbinary](max) NULL
);
INSERT INTO #tmpDiagrams SELECT * FROM sysdiagrams;

EXEC sp_MSforeachtable 'DELETE ?';

EXEC sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL';
EXEC sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL';

EXEC sp_MSforeachtable 'IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1
    DBCC CHECKIDENT (''?'', RESEED, 0);';
PRINT '### Cleared all tables ###';

INSERT INTO sysdiagrams ([name], [principal_id], [version], [definition])
SELECT [name], [principal_id], [version], [definition] FROM #tmpDiagrams;

DROP TABLE #tmpDiagrams;

Caveats worth knowing in 2026

Dev and test boxes only. This is a sledgehammer. In CI/CD the equivalent of “empty your database” should be a rebuild from migrations or a DACPAC deploy, which also guarantees the schema you end up with is the one in source control.

sp_MSforeachtable quirks. It processes tables in no guaranteed order and silently skips tables it can’t handle. If you need auditability, a plain cursor over sys.tables building dynamic SQL does the same job with nothing undocumented involved.

Related: to see which constraints exist and which have been disabled or left untrusted — the natural follow-up after mass deletes — see the constraint-listing query and DBCC CHECKCONSTRAINTS.