Disable foreign keys

,

Ever need to disable some foreign key during the load of new data in some tables? (Eq: for a snapshot).
The following just does that using a parameter (0 for disable and 1 for enable)

drop procedure IoForeignKeys


--IOForeignKeys 0




--IOForeignKeys 1


CREATE   procedure IOForeignKeys @PutFK integer
as

declare @IdFK integer
declare @ForeignKey sysname
declare @ChildTable sysname
declare @ParentTable sysname
declare @ParentColumn sysname
declare @ChildColumn sysname
declare @ParentColumns varchar(1000)
declare @ChildColumns varchar(1000)
		

		
		if exists (select 1 from sysobjects where type = 'U' and Name ='metForeignKeys')
			drop table metForeignKeys
		
		
		select * into metForeignKeys from 
		(
		select 
			FK.constid as IdFK,
			FK.KeyNo,
			sofk.name as [Foreign Key Name],
			soch.name as [Child Table],
			scch.name as [Child Column],
			sopa.name as [Parent Table],
			scpa.name as [Parent Column]
		from 
			sysforeignkeys FK
		
		inner join sysobjects sofk on FK.constId = sofk.id
		inner join sysobjects soch on FK.fkeyid = soch.id
		inner join syscolumns scch on FK.fkeyid = scch.id and FK.fkey = scch.colid
		inner join sysobjects sopa on FK.rkeyid = sopa.id
		inner join syscolumns scpa on FK.rkeyid = scpa.id and FK.rkey = scpa.colid
		)T
		
		declare met_C_Delete cursor for select distinct IdFK from metForeignKeys
		open met_C_Delete
		
		fetch next from met_C_Delete into @IdFK
		while @@Fetch_Status = 0
		begin
			select @ForeignKey = [Foreign Key Name], @ChildTable = [Child Table], @ParentTable = [Parent Table] 
				from metForeignKeys where IdFK = @IdFK
			
			if @PutFK = 0
				EXEC( 'alter table [' + @ChildTable + '] with check nocheck constraint ' + @ForeignKey)
			else
				EXEC( 'alter table [' + @ChildTable + '] with check check constraint ' + @ForeignKey)
			fetch next from met_C_Delete into @IdFK
		end
		
		close met_C_Delete
		deallocate met_C_Delete








GO

Rate

4 (2)

Share

Share

Rate

4 (2)