delete all constraints for a table

Question

delete all constraints for a table

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

steve block SSCertifiable Points: 7698 More actions · Answer 1

The only time I ever used the script before was to reload a test database from a production database with dts and the constraints where causing problems when the data was being deleted.

I don't restore the production database to the test so I can selectively copy data. I can have multiple projects going on and there may be some tables that I don't want touched in the test environment.

I have since learned that there is an easier way to just disable and reenable the constraints.

I also have triggers that populate audit trail tables so I disable the triggers as well.

There are only a couple of us that work in the test database so it is easy enough to coordinate.

-- Disable all constraints and triggers

sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

go

sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"

go

--Enable all constraints and triggers

sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

go

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

go

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

Perfect... thanks for that and great answer... good code, too!

Amrita??? How about you? You started this thread... why do YOU need to do this?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

Amrita?

I just love it when the OP doesn't respond... stab'n'grab.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 4

Jeff, it could be due to the fact that the op posed the question some 2+ years ago

/Kenneth

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

Doh! Came up as an "active" thread and didn't even think of checking the original date ... thanks for the heads up, Ken .

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)