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

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