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'
Comments
17 responses to “Drop all foreign keys”
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
Excellent Script!
Comments have been turned off for this post, when I look at the comment spam about 99% of it comes in on this post so just to frustrate those scumbags no more comments. You could write me if you have something to say my user name is tom and the domain is in the URL, leave off the www.