Disable foreign keys in all tables

[timscronin]

Is there a way to disable all foreign keys in a database and then enable them

 

[wilk]

here is some code to do what you want:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[disablefk]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[disablefk]
GO



CREATE PROCEDURE disablefk AS
declare @table_name varchar(32)
declare @constraint_name varchar(64)
declare @sqlstmt varchar(256)

declare c cursor for
select a.table_name,a.constraint_name from INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE a,
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS b
where a.constraint_name = b.constraint_name
open c
fetch next from c
into @table_name,@constraint_name
while @@fetch_status =0
begin
print @table_name
set @sqlstmt = 'alter table ' + @table_name + ' nocheck constraint ' + @constraint_name
exec (@sqlstmt)
fetch next from c
into @table_name,@constraint_name
end
close c
deallocate c
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO




if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[enablefk]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[enablefk]
GO

CREATE PROCEDURE enablefk AS
declare @table_name varchar(32)
declare @constraint_name varchar(64)
declare @sqlstmt varchar(256)

declare c cursor for
select a.table_name,a.constraint_name from INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE a,
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS b
where a.constraint_name = b.constraint_name
open c
fetch next from c
into @table_name,@constraint_name
while @@fetch_status =0
begin
print @table_name
set @sqlstmt = 'alter table ' + @table_name + ' check constraint ' + @constraint_name
exec (@sqlstmt)
fetch next from c
into @table_name,@constraint_name
end
close c
deallocate c
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



simply create these stored procedures and the exec them as you wish