LLI Wiki — My Digital Knowledge Base

Disable constraints and delete all data from a database

Disable foreign keys and triggers with sp_MSforeachtable, empty the tables, then re-enable everything.

The undocumented procedure sp_MSforeachtable runs a command for each table of the current database. It can disable foreign key constraints, empty the tables, then enable the constraints again.

Disable the constraints

EXEC sp_MSforeachtable "print 'ALTER TABLE ? NOCHECK CONSTRAINT all'"

The print keyword displays the statements without running them.

Enable the constraints

exec sp_MSforeachtable @command1="print '?'", @command2="print 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'"

Delete all data

The following script disables the triggers and the constraints, deletes the rows of each table, then enables the constraints and the triggers again.

Warning: this script deletes all the data of the database. Run it only on a test or development database.

EXEC sp_MSForEachTable 'DISABLE TRIGGER ALL ON ?'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'DELETE FROM ?'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'ENABLE TRIGGER ALL ON ?'
GO
Mastodon LLIMastodon SocialFosstodon