This entry was posted on Thursday, March 17th, 2011 at 11:44 am and is filed under SQL Server, T-SQL. You can follow any responses to this entry through the RSS 2.0 feed.
You can leave a response, or trackback from your own site.
5 Responses to SQL Server: Drop all Foreign Keys that Reference a Table
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
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..
The text, pictures and media used in this blog and its associated pages copyright Kramii, unless stated otherwise. Written permission is required if you wish to reproduce them in any way.
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
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 😉
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!
Website applications software…
[…]SQL Server: Drop all Foreign Keys that Reference a Table « All Wrong[…]…
Thanks for that – I was having trouble with my database and this has helped!