SQL Server: Drop all Foreign Keys that Reference a Table

This post has moved to Yagni.net.

 

Advertisements

5 Responses to SQL Server: Drop all Foreign Keys that Reference a Table

  1. Blaine Manock says:

    Your script didn’t work for me, just ended in an infinite loop… Not sure if that was intended or not? Is it supposed to be wrong LOL..

    Anyway, here’s mine:

    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

  2. Blaine Manock says:

    I take that back 🙂

    I wanted to see the output so removed the line that executes on each loop.. so it obviously goes into an infinite loop as your procedure checks the constraints on every iteration..

    Mine’s faster though 😉

  3. Education says:

    Excellent site. A lot of useful info here. I am sending it to several pals ans also sharing in delicious. And naturally, thanks for your effort!

  4. Website applications software…

    […]SQL Server: Drop all Foreign Keys that Reference a Table « All Wrong[…]…

  5. cheap dvds says:

    Thanks for that – I was having trouble with my database and this has helped!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: