How can I drop all the constraints in a database

Question

Post reply

How can I drop all the constraints in a database

matey

SSCommitted

Points: 1845
More actions
April 12, 2006 at 3:48 am

#112751

Hi all,
Does anyone know of a script I can run that can drop all the constraints in a database kindly share with me. Thanks
Regards
Akin

Viewing 8 posts - 1 through 7 (of 7 total)

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

karthikeyan-146504 Ten Centuries Points: 1055 More actions · Answer 1

ALTER TABLE <yourtablename> DROP CONSTRAINT <constraint_names>

Kindest Regards,

karthik

matey SSCommitted Points: 1845 More actions · Answer 2

Thanks for your prompt response but the script will only drop the constraint one table at a time. I need what can drop all constraints in a database at once...

John Rowan SSC Guru Points: 56440 More actions · Answer 3

There isn't a canned script that will do what you are asking for. You must create one using the syntax that karthik suggested. Depending on what types of constraints you intend to drop, you may be able to generate a good portion of your drop contraints script using EM>>generate sql script.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Luiz Carlos SSC-Addicted Points: 448 More actions · Answer 4

select 'ALTER TABLE ' + so.name + ' DROP CONSTRAINT ' + sc.name

from sysobjects as so

inner join sysobjects as sc on sc.parent_obj = so.id

where sc.xtype = 'C'

order by so.name

Vlaicu CRISAN-311803 Grasshopper Points: 19 More actions · Answer 5

Similar to Luis brilliant solution, but without the where clause:

select 'ALTER TABLE ' + so.name + ' DROP CONSTRAINT ' + sc.name

from sysobjects so

inner join sysobjects sc on sc.parent_obj = so.id

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

The scripts offered will certainly work (although you may want to add WHERE so.XTYPE = 'U' to filter on tables only), but tell me please... why would you want 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)

matey SSCommitted Points: 1845 More actions · Answer 7

Thanks guys for your fabulous response and I've found a way from your posts which is to drop all constraints with dropping foreign keys first and then other keys accordingly. I need to do this because I want to drop some tables in a database and send the remaining tables to DVD and I don't need the constraints all I require is the data within the tables for export to DVD. So it does not matter if I drop all the constraints as I don't need them anyway...

Thanks again and you've all made my day