[ETL] Disabling foreign key constraints

When you're doing bulk loads, the Foreign Keys can bring you to a standstill. I can't load that one without this one going first, etc.

Quick way around it: disable all the constraints, load, then enable. Thanks to "sqladmin" at http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=60&threadid=48410&enterthread=y for this one, as well as Erland for the WITH CHECK.

I love sp_MSforeach[...] - makes it very easy to walk through databases/tables. Indispensable.

One note, though: even if you disable the constraints, on SQL Server 2005 SP2 (and possibly others; haven't thoroughly tested yet) you still cannot TRUNCATE the table. You can DELETE from it, just not TRUNCATE.

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

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

To Enable all Constraints (the WITH CHECK forces it to verify all the constraints are now good. Very important, as it helps ensure good execution plans)
exec sp_MSforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'

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