Disable Foreign keys

,

Updated version of the script by Chrislis http://www.sqlservercentral.com/scripts/Replication/31556/

The code is originally credited to Chrislis - I've just amended it!

  • Uses SQL2005 system views rather than SQL2000 system tables
  • Includes support for schemas

 

/*
Updated version of the script by Chrislis 
[http://www.sqlservercentral.com/scripts/Replication/31556/]

Uses SQL2005 system views rather than SQL2000 system tables;
and includes support for schemas
*/
drop procedure IoForeignKeys
go
--IOForeignKeys 0
--IOForeignKeys 1
CREATE procedure IOForeignKeys @PutFK integer
as
declare @IdFK integer
declare @ForeignKey sysname
declare @ChildSchema sysname
declare @ParentSchema 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.constraint_object_id as IdFK,
            --FK.KeyNo,
            sofk.name as [Foreign Key Name],
            schema_name(soch.schema_id) as [Child Schema],
            soch.name as [Child Table],
            scch.name as [Child Column],
            schema_name(sopa.schema_id) as [Parent Schema],
            sopa.name as [Parent Table],
            scpa.name as [Parent Column]
        from 
            sys.foreign_key_columns FK
        inner join sys.objects sofk on FK.constraint_object_id = sofk.object_id
        inner join sys.objects soch on FK.parent_object_id = soch.object_id
        inner join sys.columns scch on FK.parent_object_id = scch.object_id and FK.parent_column_id = scch.column_id
        inner join sys.objects sopa on FK.referenced_object_id = sopa.object_id
        inner join sys.columns scpa on FK.referenced_object_id = scpa.object_id and FK.referenced_column_id = scpa.column_id
    )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], @ChildSchema = [Child Schema]
                from metForeignKeys where IdFK = @IdFK
            
            if @PutFK = 0
                EXEC( 'alter table [' + @ChildSchema + '].[' + @ChildTable + '] with check nocheck constraint ' + @ForeignKey)
            else
                EXEC( 'alter table [' + @ChildSchema + '].[' + @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

2 (1)

Share

Share

Rate

2 (1)