WilberBeast

I recently came across a situation where part of the data processing would put the data temporarily into a non-referential state. In order to accommodate this, without scripting Drop/Create statements which could get out of sync with other development work, I put together the following stored procedure which enables/disables check constraints.

Feel free to use/copy/amend as appropriate, but please let me have your comments/updates.

Create Procedure [etl].[SetCheckContraintsState]
/******************************************************************************
Description:
    Enables/Disables any check contraints that reference the target table.
    This is useful for when referenced data is going to be temporarily modified
    in a way that will invalidate any check constraints.
History:
    02-Jun-2015 S.Wilber
        Created
******************************************************************************/
    @target_schema_name sysname
   ,@target_table_name sysname
   ,@do_set_enabled bit
As

-- declare variables
Declare     @constraint_name sysname
           ,@referencing_schema_name sysname
           ,@referencing_table_name sysname
           ,@sql nvarchar(max)

Declare @constraints table
(
    ConstraintName sysname
   ,ReferencingSchemaName sysname
   ,ReferencingTableName sysname
)

Insert      @constraints (ConstraintName, ReferencingSchemaName, ReferencingTableName)
Select      fk.name As ConstraintName
           ,sch.name As ReferencingSchemaName
           ,t.name As ReferencingTableName
From        sys.foreign_keys fk
Inner Join  sys.tables t On (t.object_id = fk.parent_object_id)
Inner Join  sys.schemas sch On (sch.schema_id = t.schema_id)
Where       fk.referenced_object_id = Object_ID(QuoteName(@target_schema_name) + '.' + QuoteName(@target_table_name))

Declare     constraint_cursor Cursor Fast_Forward For
Select      ConstraintName
           ,ReferencingSchemaName
           ,ReferencingTableName
From        @constraints

Open constraint_cursor
Fetch From constraint_cursor Into @constraint_name, @referencing_schema_name, @referencing_table_name

While @@Fetch_Status = 0
  Begin
    If @do_set_enabled = 1
        Set @sql = 'Alter Table ' + QuoteName(@referencing_schema_name) + '.' + QuoteName(@referencing_table_name) + ' With Check Check Constraint ' + QuoteName(@constraint_name)
    Else
        Set @sql = 'Alter Table ' + QuoteName(@referencing_schema_name) + '.' + QuoteName(@referencing_table_name) + ' NoCheck Constraint ' + QuoteName(@constraint_name)

    Exec sp_executesql @sql

    -- get the next constraint
    Fetch From constraint_cursor Into @constraint_name, @referencing_schema_name, @referencing_table_name
End

Close constraint_cursor
Deallocate constraint_cursor