We help IT Professionals succeed at work.

drop all foreign keys in DB

jung1975
jung1975 asked
on
Medium Priority
555 Views
Last Modified: 2012-06-27
how can I drop all foreign keys in DB ? i would like to do it dynamically..
how can i do this?
Comment
Watch Question

CERTIFIED EXPERT
Awarded 2008
Awarded 2008
Commented:
use master
go
CREATE PROCEDURE [dbo].[sp_ShowForeignKeyObjects]
AS
      SELECT
            OBJECT_NAME(constid) AS ConstraintName,
            OBJECT_NAME(fkeyid) + '.' + COL_NAME(fkeyid, fkey) AS ForeignKeyObject,
            OBJECT_NAME(rkeyid) + '.' + COL_NAME(rkeyid, rkey) AS ReferenceKeyObject,
            COL_NAME(fkeyid, fkey) AS ForeignKeyColumn,
            COL_NAME(rkeyid, rkey) AS ReferenceKeyColumn,
            constid AS ConstraintID,
            OBJECT_NAME(fkeyid) AS ForeignKeyTable,             
            fkeyid AS ForeignKeyID,
            OBJECT_NAME(rkeyid) AS ReferenceKeyTable,
            rkeyid AS ReferenceKeyID,
            keyno AS KeySequenceNumber
      FROM
            sysforeignkeys
      ORDER BY
            OBJECT_NAME(rkeyid) ASC, COL_NAME(rkeyid, rkey)

GO
use yourdbname
go
IF OBJECT_ID('tempdb..#FK')>0
      DROP TABLE #FK

IF OBJECT_ID('tempdb..#Const')>0
      DROP TABLE #Const

CREATE TABLE #FK
(
      ConstraintName VARCHAR(255),
      ForeignKeyObject VARCHAR(255),
      ReferenceObject VARCHAR(255),
      ForeignKeyColumn VARCHAR(255),
      ReferenceKeyColumn VARCHAR(255),
      ConstraintID INT,
      ForeignKeyTable VARCHAR(255),
      ForeignKeyID INT,
      ReferenceKeyTable VARCHAR(255),
      ReferenceKeyID INT,
      KeySequenceNumber SMALLINT
)

--master..sp_helptext 'sp_foreignkeyobjects'
CREATE TABLE #Const
(
      ConstraintID INT,
      FBuildField VARCHAR(2000) DEFAULT(''),
      RBuildField VARCHAR(2000) DEFAULT(''),
      CountField SMALLINT
)
INSERT INTO #FK
EXEC sp_Showforeignkeyobjects


--
-- select * from #fk
SET NOCOUNT ON
DECLARE  tempcursor
CURSOR
READ_ONLY
FOR

      select       
            f.ConstraintName ,
            f.ForeignKeyObject ,
            f.ReferenceObject ,
            f.ForeignKeyColumn ,
            f.ReferenceKeyColumn,
            f.ConstraintID ,
            f.ForeignKeyID ,
            f.ReferenceKeyID ,
            f.KeySequenceNumber,
            f.ForeignKeyTable,
            f.ReferenceKeyTable
      from #FK AS  f
            INNER JOIN
            (
                  SELECT ConstraintID, MAX(KeySequenceNumber) AS MaxSeq
                  FROM #FK AS  k
                  --WHERE f.ConstraintID = k.ConstraintID AND f.KeySequenceNumber = k.MaxSeq
                  GROUP BY k.ConstraintID
            )b ON f.ConstraintID = b.ConstraintID AND f.KeySequenceNumber = b.MaxSeq
            
--select * from #fk where constraintid = 738361945


DECLARE
      @ConstraintName VARCHAR(255),
      @ForeignKeyObject VARCHAR(255),
      @ReferenceObject VARCHAR(255),
      @ForeignKeyColumn VARCHAR(255),
      @ReferenceKeyColumn VARCHAR(255),
      @ConstraintID INT,
      @ForeignKeyID INT,
      @ReferenceKeyID INT,
      @KeySequenceNumber SMALLINT,
      @ForeignKeyTable VARCHAR(255),
      @ReferenceKeyTable VARCHAR(255)

OPEN tempcursor

      FETCH NEXT FROM tempCursor INTO
      @ConstraintName ,
      @ForeignKeyObject ,
      @ReferenceObject ,
      @ForeignKeyColumn ,
      @ReferenceKeyColumn,
      @ConstraintID ,
      @ForeignKeyID ,
      @ReferenceKeyID,
      @KeySequenceNumber,
      @ForeignKeyTable ,
      @ReferenceKeyTable

WHILE (@@fetch_status <> -1)
BEGIN

      DECLARE  tempcursor2
      CURSOR
      READ_ONLY
      FOR
      
            SELECT ConstraintID, ForeignKeyColumn, ReferenceKeyColumn, KeySequenceNumber
            FROM #FK
            WHERE ConstraintID = @ConstraintID

            ORDER BY ConstraintID, KeySequenceNumber ASC
      
      DECLARE @ConstraintID2 INT, @ForeignKeyColumn2 VARCHAR(255), @ReferenceKeyColumn2 VARCHAR(255), @KeySequenceNumber2 SMALLINT
      DECLARE @FKeyBuildField VARCHAR(1000), @RKeyBuildField VARCHAR(1000), @Cnt SMALLINT
      
      OPEN tempcursor2
      PRINT '--------------------------------------------------------'
      SELECT @FKeyBuildField = '', @RKeyBuildField = '', @Cnt = 0

      FETCH NEXT FROM tempcursor2 INTO @ConstraintID2 , @ForeignKeyColumn2 , @ReferenceKeyColumn2 , @KeySequenceNumber2
      WHILE (@@fetch_status <> -1)
      BEGIN
            SET @Cnt = @Cnt + 1
            SELECT @FKeyBuildField = @FKeyBuildField  + ISNULL(@ForeignKeyColumn2,'')+
                  CASE
                        WHEN @ForeignKeyColumn2 IS NULL THEN ''
                  ELSE  
                        CASE WHEN @KeySequenceNumber = @KeySequenceNumber2 THEN '' ELSE ',' END
                  END

            SELECT @RKeyBuildField = @RKeyBuildField  + ISNULL(@ReferenceKeyColumn2,'')+
                  CASE
                        WHEN @ReferenceKeyColumn2 IS NULL THEN ''
                  ELSE  
                        CASE WHEN @KeySequenceNumber = @KeySequenceNumber2 THEN '' ELSE ',' END
                  END
      
            INSERT INTO #Const
            (      
                  ConstraintID ,
                  FBuildField ,
                  RBuildField,
                  CountField  
            )
            VALUES
            (
                  @ConstraintID,
                  @FKeyBuildField,
                  @RKeyBuildField,
                  @Cnt
            )

            FETCH NEXT FROM tempcursor2 INTO @ConstraintID2 , @ForeignKeyColumn2 , @ReferenceKeyColumn2 , @KeySequenceNumber2
      END
      PRINT @FKeyBuildField + ' | ' + CAST(@KeySequenceNumber AS VARCHAR(100))+ ' | ' + CAST(@KeySequenceNumber2 AS VARCHAR(100))
      CLOSE tempcursor2
      DEALLOCATE tempcursor2

      FETCH NEXT FROM tempCursor INTO
      @ConstraintName ,
      @ForeignKeyObject ,
      @ReferenceObject ,
      @ForeignKeyColumn ,
      @ReferenceKeyColumn,
      @ConstraintID ,
      @ForeignKeyID ,
      @ReferenceKeyID ,
      @KeySequenceNumber ,
      @ForeignKeyTable ,
      @ReferenceKeyTable
END

CLOSE tempcursor
DEALLOCATE tempcursor

select 'ALTER TABLE [' + FKTable + '] DROP CONSTRAINT ' + OBJECT_NAME(a.ConstraintID) AS DropKeys,
'ALTER TABLE [' + FKTable + '] WITH NOCHECK ADD CONSTRAINT ' + OBJECT_NAME(a.ConstraintID) + ' FOREIGN KEY(' + FBuildField + ') REFERENCES ' + RKTable + '(' + RBuildField+')' AS BuildKeys
,*
from #Const a
join
(
      select ConstraintID, max(countfield) as maxcount
      from #Const
      group by ConstraintID
) b on a.ConstraintID = b.ConstraintID  and a.countfield = b.maxcount
join
(
select distinct constraintid, object_name(foreignkeyid) as FKTable, object_name(referencekeyid) AS RKTable from #fk
) c on a.constraintid = c.constraintid

DROP TABLE #Const

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.