[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 545
  • Last Modified:

drop all foreign keys in DB

how can I drop all foreign keys in DB ? i would like to do it dynamically..
how can i do this?
0
jung1975
Asked:
jung1975
1 Solution
 
chapmandewCommented:
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
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now