Drop all Foreign Keys that Reference a Table

CREATE PROCEDURE [dbo].[_dropFKs]
@tableName nvarchar(max)
AS
BEGIN

DECLARE
@sql NVARCHAR(MAX),
@targetTableName NVARCHAR(MAX),
@foreignKeyName NVARCHAR(MAX)

DECLARE curs CURSOR FOR
SELECT OBJECT_NAME (f.parent_object_id), 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

OPEN curs
FETCH NEXT FROM curs INTO @targetTableName, @foreignKeyName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘ALTER TABLE ‘ + @targetTableName + ‘ DROP CONSTRAINT ‘ + @foreignKeyName
PRINT @sql
EXEC sp_executesql @sql
FETCH NEXT FROM curs INTO @targetTableName, @foreignKeyName
END

CLOSE curs
DEALLOCATE curs

END
GO

Leave a Reply

Your email address will not be published.

Powered by themekiller.com