SQL Server: Drop all Foreign Keys that Reference a Table

Introduction

Before you drop a table in SQL Server, you must first drop all the foreign keys that reference that table. The following SQL scripts may help with this task.

Step 1:  Run the following to create a Stored Procedure that will perform the drop:

[sourcecode language="sql"]
CREATE PROCEDURE [dbo].[_dropFKs]
  @tableName nvarchar(max)
AS
BEGIN
  DECLARE @sql nvarchar(MAX)
  WHILE EXISTS (
    SELECT
      f.name AS ForeignKey,
      OBJECT_NAME(f.parent_object_id) AS TableName,
      COL_NAME(fc.parent_object_id,
      fc.parent_column_id) AS ColumnName,
      OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
      COL_NAME(fc.referenced_object_id,
      fc.referenced_column_id) AS ReferenceColumnName
    FROM
      sys.foreign_keys AS f
    INNER JOIN
      sys.foreign_key_columns AS fc
        ON f.OBJECT_ID = fc.constraint_object_id
    WHERE
      OBJECT_NAME (f.referenced_object_id) = @tableName
  )
  BEGIN
    SELECT @sql =
      'ALTER TABLE ' +
      OBJECT_NAME (f.parent_object_id) +
      ' DROP CONSTRAINT ' +
      f.name +
      ''
    FROM
      sys.foreign_keys AS f
    INNER JOIN
      sys.foreign_key_columns AS fc
        ON f.OBJECT_ID = fc.constraint_object_id
    WHERE
      OBJECT_NAME (f.referenced_object_id) = @tableName

    PRINT @sql

    EXEC sp_executesql @sql
  END
END
GO
[/sourcecode]

Step 2: Run the following to drop all FKs that reference the target table:

[sourcecode language="sql"]
EXEC _dropFKs 'TargetTableName'
[/sourcecode]

Step 3: Run the following to delete the Stored Procedure that was created in step 1:

[sourcecode language="sql"]
IF EXISTS (
  SELECT
    *
  FROM
    sysobjects
  WHERE
    id = object_id(N'[dbo].[_dropFKs]')
  AND
    OBJECTPROPERTY(id, N'IsProcedure') = 1
)
BEGIN
  DROP PROCEDURE [dbo].[_dropFKs]
END
GO
[/sourcecode]
Be Sociable, Share!
This entry was posted in SQL Server. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *