How to drop all the foreign key constraints in a database

Sometimes, foreign keys become annoying in a secondary database where reporting is the main purpose while integrity is not. So dropping all the foreign keys may become a user request from time to time.

The keyword to this question is the catalog view "sys.foreignkeys". And you can go to here to read about about how to retrieve all the foreign key constraints from a database and why catalog view is preferred.

Here is the script to delete all the foreign keys from a database.

use db_name
go

declare @fk_name varchar(100)
declare @tb_name varchar(100)
declare @cmd varchar(200)

declare cur_fk cursor
for
select name as ForeignKey_Name, OBJECT_NAME(parent_object_id) as Table_Name from sys.foreign_keys
order by Table_Name


open cur_fk

fetch next from cur_fk into @fk_name, @tb_name
while @@FETCH_STATUS = 0
begin
set @cmd = 'alter table ' + @tb_name + ' drop constraint ' + @fk_name
select @cmd
exec (@cmd)
fetch next from cur_fk into @fk_name, @tb_name
end

close cur_fk
deallocate cur_fk