Remove referential integrity? 1

[don2241]

Hi!

1. I want to be able to run a script that removes all referential integrity constrains on my whole DB

2. Do some changes

3. Run a script to reinstate the referential integrity contrains on the DB again.

Is this possible to do?

/M.

 

[entaroadun]

Certainly.

You can script ALTER TABLE statements that DELETE foreign key CONSTRAINTs. You can also put them back after.

I don't know of any command that will automatically drop all constraints of a certain type, though. I'm sure you can write an SP to automate the deletes with the constraint and table names given in the information_schema.constraints view.

 

[don2241]

Thanks mate!

I actually found a sp_ function that disables both constrains and triggers.

exec sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
exec sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"


Plus a sp_ function that instates the constrains again plus printing out an error report

exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? CHECK CONSTRAINT all"
exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? ENABLE TRIGGER all"

Thanks anyway
/M.