want to disable constraints or foreign keys?

Last Post 13 Jun 2008 04:38 PM by andeezlemoyo. 5 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
sqladmin
New Member
New Member

--
19 Jul 2006 08:37 AM
you'll need the procedure: sp_msforeachtable
if you don't have this, just open query analyzer, and run the following
to get the 'create' script for the sp.

use master
go
sp_helptext sp_MSForeachtable

---------------------------------------------


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

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

## execute updates here...

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

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

hope this is useful

cheers.
SQLUSA
New Member
New Member

--
30 Aug 2006 09:49 PM
Neat scripts...but is it useful?

Doubt it...Constrainsts and triggers are for data integrity enforcement.

Disabling them would get dirty data into the database.

Kalman Toth, Database, Data Warehouse and BI Architect
http://www.sqlusa.com/order2005/
The Best SQL Server 2005 Training in the World
sanjayattray
New Member
New Member

--
31 Aug 2006 07:06 AM
Very usefull and good script, as always. thanks mike.
sanjayattray
New Member
New Member

--
31 Aug 2006 07:07 AM
one quick question to you mike, where did you get all these scripts from?
sqladmin
New Member
New Member

--
01 Sep 2006 08:53 AM
most scripts i find in old notes, and could be authentic, or could be modded from scripts elsewhere.
i'm trying to make a habit of posting them all up in forums. figure it 'might' help the search mechanics
they have yet to improve in this site.
andeezlemoyo
New Member
New Member

--
13 Jun 2008 04:38 PM
This is great but there's one HUGE caveat.

In the real world you may have constraints that are not enforced...the enable script makes no distinction, of course, so it essentially resets all constraints to enforce when you run the reenable script.

it's still cool though.

-andy


Acceptable Use Policy
---