Anders Lybeckers Weblog!

We (my colleagues at Miracle and I) are currently in the process of upgrading a customer’s solutions. One of task is migrating 100 gigs of data into an existing database schema. This can easily become a tedious task and can take considerable calendar time. Therefore we decided to disable all constraints on the database, move the data and then enable them again, as we know the data integrity is sound.
We could have written a script that gets all the constraints in the database and generates a SQL script, but there is an easier way…

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

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

That’s easy…

I found this on the Internet somewhere – the sp_MSforeachtable procedure is nowhere to be found in books online.
You can check if foreign keys and check constrains are disabled via the sys.foreign_keys  and sys.check_constraints views.

Another obvious triviality in our situation is disabling and enabling triggers – that is easy too:

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

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

 Anders Lybecker is an chief architect at Kring Development A/S, a consultancy firm in Copenhagen, Denmark. He holds a degree in software engineering specializing in software development. His primary expertises are the Microsoft .Net framework and SQL Server which he has been working with since the start of this century! He enjoys discussing technical topics, teaching and speaking at conferences.

Related posts:

1. How to check for and enable read committed snapshot isolation
2. New blog – NoTech

Tags: SQL Server

This entry was posted on Thursday, August 21st, 2008 at 8:38 pm and is filed under SQL Server. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.