Technical Article

Drop all foreign keys

,

Drops all the foreign keys in a database. This is useful when rebuilding data warehouses.

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

ALTER  Procedure Drop_Foreign_Keys
AS
/* Create a variable to hold dynamic SQL statement */declare @Select_SQL as nvarchar(500)

/* Drop Foreign Key table if exists - this is temporary */if exists (select * from sysobjects where id = object_id('Foreign_Key_Table') and sysstat & 0xf = 3)
drop table Foreign_Key_Table

/* Get foreign key info for one table */select top 1
       object_name(fkeyid) as foreign_key_table, 
       object_name(rkeyid) as primary_key_table,
       object_name(constid) as [name]
Into Foreign_Key_Table
 from sysforeignkeys
where OBJECTPROPERTY(rkeyid, N'IsUserTable') = 1

/* Drop foreign key found and get details on next until all have been dropped */While (select count(*) from foreign_key_table) > 0
 begin
   set @Select_SQL = 'alter table '
                + (select foreign_key_table + ' drop ' + name from Foreign_Key_Table)
   exec sp_executesql @Select_SQL   

   truncate table Foreign_Key_Table
   Insert into Foreign_Key_Table
   select top 1
          object_name(fkeyid) as foreign_key_table, 
          object_name(rkeyid) as primary_key_table,
          object_name(constid) as [name]
     from sysforeignkeys
    where OBJECTPROPERTY(rkeyid, N'IsUserTable') = 1
   
 end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Read 1,109 times
(8 in last 30 days)

Rate

4.67 (3)

You rated this post out of 5. Change rating

Share

Share

Rate

4.67 (3)

You rated this post out of 5. Change rating