Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2002
    Posts
    2

    Unanswered: drop constraints

    Hi,

    I'm trying to update some tables, but there are constraints on them that need to be removed first. As I didn't create the DB and tables myself, I used the 'Generate SQL script' to get all constraints and their name.

    I then had a look at the 'sysobject' table, and found some constraints (FK__ ....) listed in the script, but not all of them. Is there another way to to get all constraints on the DB ??

    can you suggest the best way to drop the constrains? I was going to use something like:

    declare @mytest char(50)
    set @mytest=(select name from sysobjects where name like "FK__Item__attrib%")
    EXEC( 'alter table item drop constraint '+@mytest)


    vincent

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    I use this code to remove PRIMARY and FORIEGN KEY constraints plus drop all of my indexes, however I removed the DROP INDEX part
    Code:
    SET NOCOUNT ON
    DECLARE @SQLCmd varchar(255)
    
    DECLARE DelCur CURSOR
    FOR
    SELECT  CASE
                WHEN OBJECTPROPERTY(OBJECT_ID(i.name), 'IsPrimaryKey') = 1
                 THEN 'ALTER TABLE ' + + o.name + ' DROP CONSTRAINT ' + i.name
                WHEN OBJECTPROPERTY(OBJECT_ID(i.name), 'IsForiegnKey') = 1
                 THEN 'ALTER TABLE ' + + o.name + ' DROP CONSTRAINT ' + i.name
            END
    FROM    sysindexes  i,
            sysobjects  o
    WHERE   o.id            = i.id
    AND     OBJECTPROPERTY(o.id, 'IsUserTable') = 1
    AND     OBJECTPROPERTY(o.id, 'IsMSShipped') = 0
    AND     i.indid         BETWEEN 1 AND 254
    AND     INDEXPROPERTY(o.id, i.name, 'IsStatistics') = 0
    AND     (
            OBJECTPROPERTY(OBJECT_ID(i.name), 'IsPrimaryKey') = 1 OR
            OBJECTPROPERTY(OBJECT_ID(i.name), 'IsForiegnKey') = 1
            )
    
    OPEN DelCur
    
    FETCH DelCur INTO @SQLCmd
    
    WHILE (@@fetch_status = 0)
    BEGIN
        PRINT @SQLCmd
        EXEC (@SQLCmd)
        FETCH DelCur INTO @SQLCmd
    END
    
    CLOSE DelCur
    DEALLOCATE DelCur
    GO
    MCDBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •