Technical Article

Drop foreign keys from a database

,

This script will drop all of the foreign keys on user tables in the database it is executed in. This is useful as a pre-load process.

/******************************************************************
===================================================================
WHENWHOWHAT
2005.05.23clambertcreated script
===================================================================

Purpose: This script drops foreign keys on user tables in the
         database the script is executed in.
******************************************************************/

set nocount on


-- declare variables
declare @FKeyCount as smallint
declare @Counter as smallint
declare @FKeyName as varchar(256)
declare @TableName as varchar(768)

declare @fk_drop table
  (DBName                     varchar(256),
   TblName                    varchar(256),
   FKName                     varchar(256),
   Num                        smallint identity(1,1),

   primary key(DBName,TblName,FKName))


-- identify user table foreign keys to drop
insert into
   @fk_drop
select
   f.constraint_catalog,
   f.table_name,
   f.constraint_name
from
   information_schema.table_constraints f
where
   f.constraint_type = 'foreign key' and
   objectproperty(object_id(f.table_name), N'IsUserTable') = 1


-- set while loop process variables
set @FKeyCount = (select count(1) from @fk_drop)
set @Counter = 1


-- drop foreign keys
while @Counter <= @FKeyCount
begin
   set @FKeyName = (select FKName from @fk_drop where Num = @Counter)
   set @TableName = (select DBName + '.dbo.' + TblName from @fk_drop where Num = @Counter)
   exec ('alter table ' + @TableName + ' drop constraint ' + @FKeyName )
   set @Counter = @Counter + 1
      continue
end


set nocount off

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating