CODEWITHSUNDEEP
sql-server
justSteve
justSteve

Reputation: 5524

How to drop tables with no constraint checking

I need to update the data and schema from dev to staging dbs where I want to DROP/CREATE a group of tables. I need to over-ride the FK constraint checks. Looking at the MS's ALTER TABLE syntax tree - i know it's there but i can't identify the correct syntax.

@Rup: It looks like the hangup is from other tables' FKs. Is there a way to turn all constraint checking off or do i need to produce the list of tables/FKs?

Upvotes: 2

Views: 7130

Answers (2)

Ralph Shillington
Ralph Shillington

Reputation: 21098

ALTER TABLE yourtable
NOCHECK CONSTRAINT ALL

and a variation on this theme is to disable all the constraints of all the tables delete all the data, and then add all the constraints back again.

exec sp_MSforeachtable @command1='alter table ? nocheck constraint all', @whereand='and substring(o.name,1,1) <> ''_'''
exec sp_MSforeachtable @command1='delete from ?'
exec sp_MSforeachtable @command1='alter table ? check constraint all', @whereand='and substring(o.name,1,1)<> ''_'''

The nice thing here is that with all the constraints disabled, you can delete the data in any order

Upvotes: 3

Cade Roux
Cade Roux

Reputation: 89661

You can drop the constraints, drop the tables and re-create. It should also be possible to drop the tables in the correct order and re-create if there are no circular constraints.

ALTER TABLE DROP CONSTRAINT

Upvotes: 0

Related Questions