Disable and enable constraints and triggers

,

Often in development environment you want to truncate all the tables to insert new data from production, but you cannot do easily as there were a bumch of Foreign Keys and triggers. Dropping all triggers and FK's and then recreating them again is a tedious job. That to dropping and recreating FK's in a particular order. Here is a simple script to disable and enable all your constraints and trigger.

I go this script from one of my friend in sql forums. Posting here, so that it could be helpful to user commnity.

-- Disable all Constraints
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

-- Disable all Triggers
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'



-- Enable all Constraints
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

-- Enable all Triggers
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'

Rate

5 (1)

Share

Share

Rate

5 (1)