Mon 23 Jul 2007
Drop all foreign keys
Posted by tom under Programming
[15] Comments
I was working on some tables today and found the need to drop all the foreign key constraints. There is no easy function in the manager so I whipped up this little bit of SQL that will create the commands needed to do this. This will work in MS Sql Server 2000 and 2005
SELECT 'ALTER TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME +
' DROP CONSTRAINT ' + CONSTRAINT_NAME
FROM information_schema.table_constraints
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
This should work for all weird table names:
SELECT ‘ALTER TABLE ‘ + TABLE_SCHEMA + ‘.[' + TABLE_NAME +
'] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']‘
FROM information_schema.table_constraints
WHERE CONSTRAINT_TYPE = ‘FOREIGN KEY’
Concise, helpful script. Thanks.
An excellent little script. Could you wrap the script in pre tags so that you get the correct quotes when you copy/paste it?
WordPress has can button for inserting code tags, this seemed to work, at least when I copied it the quotes came out right. Thanks for the feedback.
Good point, wrapping the names in brackets would avoid issues. I always avoid naming my tables such that it would be a problem, makes life ever so much easier but one cannot count on others to do the same.
Why not take it a step further and execute the alter automatically. Especially if it is part of a maintenance job that performs a truncate on the tables.
DECLARE FK_cursor CURSOR
FOR
SELECT S.name, F.name, T.name
FROM sys.foreign_keys F
JOIN sys.tables T ON F.parent_object_id=T.object_id
JOIN sys.schemas S ON S.schema_id=T.schema_id
WHERE
T.name in (‘PayldSeg’,'PayldDest’,'Payld’,'DataPkgUsrStsHist’,'DataPkgUsr’,'DataPkg’,'DataPkgRqstQueUsr’,'ServPrcsMsg’,'DataPkgRqstQue’)
OPEN FK_cursor
DECLARE @shemaName sysname, @fkName sysname, @tableName sysname
FETCH NEXT FROM FK_cursor INTO @shemaName, @fkName, @tableName
WHILE (@@FETCH_STATUS -1)
BEGIN
PRINT ‘Dropping constraint ‘ + @fkName + ‘ on table ‘ + @shemaName + ‘.’ + @tableName
EXECUTE (‘ALTER TABLE [' + @shemaName + '].[' + @tableName + '] DROP CONSTRAINT [' + @fkName + ']‘)
FETCH NEXT FROM FK_cursor INTO @shemaName, @fkName, @tableName
END
Sorry the last post was missing the in the where and had the wrong quotes.
–DROP CONSTRAINTS
DECLARE FK_cursor CURSOR
FOR
SELECT S.name, F.name, T.name
FROM sys.foreign_keys F
JOIN sys.tables T ON F.parent_object_id=T.object_id
JOIN sys.schemas S ON S.schema_id=T.schema_id
WHERE
T.name in (‘table1′,’table2′,’table3′)
OPEN FK_cursor
DECLARE @shemaName sysname, @fkName sysname, @tableName sysname
FETCH NEXT FROM FK_cursor INTO @shemaName, @fkName, @tableName
WHILE (@@FETCH_STATUS -1)
BEGIN
PRINT ‘Dropping constraint ‘ + @fkName + ‘ on table ‘ + @shemaName + ‘.’ + @tableName
EXECUTE (‘ALTER TABLE [' + @shemaName + '].[' + @tableName + '] DROP CONSTRAINT [' + @fkName + ']‘)
FETCH NEXT FROM FK_cursor INTO @shemaName, @fkName, @tableName
END
Combining ideas [1] and [7]
DECLARE @fkdel varchar(512)
DECLARE FkCrsr CURSOR FOR
SELECT ‘ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME +
'] DROP CONSTRAINT [' + CONSTRAINT_NAME +']‘
FROM information_schema.table_constraints
WHERE CONSTRAINT_TYPE = ‘FOREIGN KEY’
open FkCrsr
fetch next from FkCrsr into @fkdel
while @@FETCH_STATUS = 0
begin
print @fkdel
exec (@fkdel)
fetch next from FkCrsr into @fkdel
end
close FkCrsr
deallocate FkCrsr
go
– ENJOY
DECLARE @sqlForeignKeys VARCHAR(MAX)
SELECT @sqlForeignKeys = ISNULL(@sqlForeignKeys,”) +
‘ALTER TABLE dbo.’ + OBJECT_NAME(FK.parent_object_id) + ‘ DROP CONSTRAINT ‘ + FK.name + ‘;’ + CHAR(10)
FROM SYS.FOREIGN_KEYS FK
PRINT(@sqlForeignKeys)
–EXEC(@sqlForeignKeys)
Added some brackets for the “weird” table names:
–ENJOY
DECLARE @sqlForeignKeys VARCHAR(MAX)
SELECT @sqlForeignKeys = ISNULL(@sqlForeignKeys,”) +
‘ALTER TABLE dbo.[' + OBJECT_NAME(FK.parent_object_id) + '] DROP CONSTRAINT [' + FK.name + '];’ + CHAR(10)
FROM SYS.FOREIGN_KEYS FK
PRINT(@sqlForeignKeys)
–EXEC(@sqlForeignKeys)
Its better to disable foreign keys. If you want to disable all constraints in the database just run this code:
– disable all constraints
EXEC sp_msforeachtable “ALTER TABLE ? NOCHECK CONSTRAINT all”
– enable all constraints
exec sp_msforeachtable @command1=”print ‘?’”, @command2=”ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all”
Going to have try this script next time this problem comes up, thanks!
Thanks Really working …
Thanks for your article.
Here’s another way to do it, without resorting to a blighted cursor.
http://www.sentientbeings.com/2012/01/drop-all-foreign-keys-on-a-database-and-optionally-drop-all-tables/
select CONSTRAINT_NAME,TABLE_NAME
into #ConstraintData
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where constraint_catalog = ‘Northwind’ AND TABLE_NAME LIKE ‘FK%’
order by CONSTRAINT_NAME
DECLARE @RowNum int
SET @RowNum = (select COUNT(*) FROM #ConstraintData)
DECLARE @Iter int
SET @Iter = 1
WHILE (@Iter <= @RowNum)
BEGIN
DECLARE @KeyName varchar(100)
SET @KeyName = (select CONSTRAINT_NAME
from (
select tbl.*, ROW_NUMBER() OVER (order by CONSTRAINT_NAME) rownum
from #ConstraintData as tbl
) seq
where seq.rownum = @Iter)
DECLARE @TableName varchar(100)
SET @TableName = (select TABLE_NAME
from (
select tbl.*, ROW_NUMBER() OVER (order by CONSTRAINT_NAME) rownum
from #ConstraintData as tbl
) seq
where seq.rownum = @Iter)
print @KeyName + ' ' + @TableName
DECLARE @SQLString NVARCHAR(500)
set @SqlString = N'ALTER TABLE ' + @TableName + ' DROP CONSTRAINT ' + @KeyName
execute sp_executesql @SqlString
set @Iter = @Iter + 1
END
DROP TABLE #ConstraintData