Tuesday, March 20, 2007

Disable all Constraints, Foreign Keys and Triggers

Disable all Constraints, Foreign Keys and Triggers for current database, Sql 2005+

To TRUNCATE tables however, you will need to drop and recreate the keys.
-- loop through tables, turning off check constraints and triggers for each
sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? NOCHECK CONSTRAINT all"
go
sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? DISABLE TRIGGER  all"
go

-- do data load or deletes here

-- loop through tables, turning back on check constraints and triggers for each
sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? CHECK CONSTRAINT all"
go
sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? ENABLE TRIGGER  all"
go

No comments: