locked
T-SQL to drop all Constraints RRS feed

  • Question

  • Does anyone have ant T-SQL that correctly drops ALL constraints in a table?  I've written T-SQL to do it but I get errors like this:  "The constraint 'PK_tlk_location' is being referenced by table 'LegLocation', foreign key constraint 'FK_tb_leg_location_tlk_location'." Therefore, it seems the constraints are not being dropped in the correct order.

    Thanks - Randy
    Friday, May 15, 2009 7:46 PM

Answers

  • Michael,

    If you correct - almost. The code didn't work as you had it written. There were two problems. The first is that 'tab.name' and 'cons.name' should have been t.name and c.name. The second is that it didn't account for the use of schemas. So, for anyone else wanting T-SQL to do this, here it is:


    declare @str varchar(max)
    declare cur cursor for

    SELECT 'ALTER TABLE ' + '[' + s.[NAME] + '].[' + t.name + '] DROP CONSTRAINT ['+ c.name + ']'
    FROM sys.objects c, sys.objects t, sys.schemas s
    WHERE c.type IN ('C', 'F', 'PK', 'UQ', 'D')
    AND c.parent_object_id=t.object_id and t.type='U' AND t.SCHEMA_ID = s.schema_id
    ORDER BY c.type

    open cur
    FETCH NEXT FROM cur INTO @str
    WHILE (@@fetch_status = 0) BEGIN
    PRINT @str
    EXEC (@str)
    FETCH NEXT FROM cur INTO @str
    END

    close cur
    deallocate cur
    Friday, May 15, 2009 8:38 PM

All replies

  • Try something like this:

    declare @str varchar(max)
    declare cur cursor for
    SELECT 'ALTER TABLE '+tab.name+' DROP CONSTRAINT '+cons.name
    FROM sys.objects c,sys.objects t
    WHERE c.type IN ('C', 'F', 'PK', 'UQ', 'D')
    AND c.parent_object_id=t.object_id and t.type='U'
    ORDER BY c.type

    open cur
    FETCH NEXT FROM cur INTO @str
    WHILE (@@fetch_status = 0) BEGIN
      EXEC (@str)
      FETCH NEXT FROM cur INTO @str
    END

    close cur
    deallocate cur

    The order should ensure the FKs are dropped before the PKs.   This drops all constraints on the DB though.  To limit it to a single table, you'd need special handling to lookup the parent of each FK....and, of course, even in this case you're going to wind up touching not only the base table, but every one which has a FK reference to it.


    Michael Asher
    • Edited by masher2 Friday, May 15, 2009 7:55 PM
    Friday, May 15, 2009 7:52 PM
  • I'm sorry - I meant to ask for T-SQL that drops all constaints in a database, not just a single table.
    Friday, May 15, 2009 8:00 PM
  • This should do it for you then.
    Michael Asher
    Friday, May 15, 2009 8:15 PM
  • Michael,

    If you correct - almost. The code didn't work as you had it written. There were two problems. The first is that 'tab.name' and 'cons.name' should have been t.name and c.name. The second is that it didn't account for the use of schemas. So, for anyone else wanting T-SQL to do this, here it is:


    declare @str varchar(max)
    declare cur cursor for

    SELECT 'ALTER TABLE ' + '[' + s.[NAME] + '].[' + t.name + '] DROP CONSTRAINT ['+ c.name + ']'
    FROM sys.objects c, sys.objects t, sys.schemas s
    WHERE c.type IN ('C', 'F', 'PK', 'UQ', 'D')
    AND c.parent_object_id=t.object_id and t.type='U' AND t.SCHEMA_ID = s.schema_id
    ORDER BY c.type

    open cur
    FETCH NEXT FROM cur INTO @str
    WHILE (@@fetch_status = 0) BEGIN
    PRINT @str
    EXEC (@str)
    FETCH NEXT FROM cur INTO @str
    END

    close cur
    deallocate cur
    Friday, May 15, 2009 8:38 PM
  • Glad it worked out for you...it's a very old script which predates schemas.
    Michael Asher
    Saturday, May 16, 2009 3:52 AM
  • Glad it worked out for you...it's a very old script which predates schemas.
    Michael Asher
    It also did not compile due to the alias names bug ;c)

    Msg 4104, Level 16, State 1, Line 3
    The multi-part identifier "tab.name" could not be bound.
    Msg 4104, Level 16, State 1, Line 3
    The multi-part identifier "cons.name" could not be bound.
    
    
    Good catch Randy, voted Michael's as helpful as yours as the proposed answer.

    Paul
    Saturday, May 16, 2009 5:52 AM