Enable/Disable all foreign keys in a database

— Foreign Key Disable / Enable
— Set @EnableFK = 0 to disable all foreign keys in a database.
— Set @EnableFK = 1 to enable all foreign keys in a database.
— If enabling FKs:
— Set @CheckExistingData = 1 to verify all existing data referenced by constraints.
—  Set @CheckExistingData = 0 to enable the constraints without checking existing data.
—  @CheckExistingData value doesn’t matter when disabling constraints.

declare @EnableFK bit
declare @CheckExistingData bit

set @EnableFK = 1
set @CheckExistingData = 0

declare @DisableSQL nvarchar(100)
declare @Action nvarchar(100)

if @EnableFK = 1
  begin
 if @CheckExistingData = 1
  set @DisableSQL = ‘with check check’
 else
  set @DisableSQL = ‘check’
 set @Action = ‘ENABLE’
  end
else
  begin
 set @DisableSQL = ‘nocheck’
 set @Action = ‘DISABLE’
  end

declare FK_Cursor cursor
   for

— Return all foreign keys in a DB
select [Name] as FKName, object_name(Parent_Obj) as TableName
from dbo.sysobjects
where XType = ‘F’

declare @FKName nvarchar(128)
declare @TableName nvarchar(128)
declare @SQL nvarchar(2000)

open FK_Cursor

fetch next from FK_Cursor into @FKName, @TableName

while @@FETCH_STATUS = 0

begin
 set @SQL = ‘alter table [dbo].[‘ + @TableName + ‘] ‘ + @DisableSQL + ‘ constraint [‘ + @FKName + ‘]’

 exec sp_executesql @SQL
 print @Action + ‘ ‘ + @TableName + ‘.’ + @FKName

 fetch next from FK_Cursor into @FKName, @TableName
end

close FK_Cursor

deallocate FK_Cursor

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: