Post reply

How can I drop all the constraints in a database

  • 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

  • April 12, 2006 at 3:57 am

    #632040

    ALTER TABLE <yourtablename> DROP CONSTRAINT <constraint_names>

    Kindest Regards,

    karthik

  • April 12, 2006 at 4:10 am

    #632043

    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...

  • April 12, 2006 at 9:39 am

    #632130

    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

  • April 13, 2006 at 6:28 am

    #632331

    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

  • April 13, 2006 at 7:22 am

    #632361

    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

  • April 16, 2006 at 9:14 pm

    #632724

    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)
    Intro to Tally Tables and Functions

  • April 17, 2006 at 4:05 am

    #632731

    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

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

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