Toggle Foreign Keys on an Object

Bulk-loading data or re-keying a table? Foreign keys get in the way. This stored procedure enables or disables every FK constraint on a given table - and, with @DisableReferences = 1, also the constraints on other tables that point at it (essential when you are deleting rows or altering IDs). Enabling uses WITH CHECK so existing data is validated.

Example: EXEC dbo.ToggleForeignKeysOnObject @ObjectName = 'dbo.Orders', @Enable = 0, @DisableReferences = 1

CREATE PROCEDURE [dbo].[ToggleForeignKeysOnObject]
(
	@ObjectName sysname,
	@Enable BIT,
	@DisableReferences bit = 0 --Also disable FKs that point at this table (useful if deleting from this table or altering IDs).
) AS
BEGIN--PROCEDURE
	SET NOCOUNT ON;

	DECLARE @FKeyName sysname
	DECLARE @RefObjName sysname
	DECLARE @tSQL varchar(max)
	
	DECLARE DirectFKs CURSOR FAST_FORWARD FOR
		SELECT
			name
		FROM
			sys.foreign_keys
		WHERE
			parent_object_id = object_id(@ObjectName)
			AND is_disabled = @Enable
	OPEN DirectFKs
	
	FETCH FROM DirectFKs INTO @FKeyName
	
	Print CASE WHEN @Enable = 0 THEN 'Disabling' ELSE 'Enabling' END + ' individual direct constraints on: ' + @ObjectName + '.'
	WHILE(@@FETCH_STATUS = 0)
	BEGIN--WHILE
		---------------------------------------------------------------------------------

		Print CASE WHEN @Enable = 0 THEN 'Disabling' ELSE 'Enabling' END + ' constraint: ' + @FKeyName + '.'
		IF(@Enable = 1)
		BEGIN--IF
			SET @tSQL = 'ALTER TABLE ' + @ObjectName + ' WITH CHECK CHECK CONSTRAINT [' + @FKeyName + ']'
		END ELSE BEGIN--IF
			SET @tSQL = 'ALTER TABLE ' + @ObjectName + ' NOCHECK CONSTRAINT [' + @FKeyName + ']'
		END--IF

		EXEC(@tSQL)

		---------------------------------------------------------------------------------
		FETCH FROM DirectFKs INTO @FKeyName
	END--WHILE

	CLOSE DirectFKs DEALLOCATE DirectFKs

	IF(@DisableReferences = 1)
	BEGIN--IF
		DECLARE ReferencingFKs CURSOR FAST_FORWARD FOR
			SELECT
				object_name(parent_object_id) AS RefObjName,
				name AS FKeyName
			FROM
				sys.foreign_keys
			WHERE
				referenced_object_id = object_id(@ObjectName)
				AND is_disabled = @Enable
		OPEN ReferencingFKs
		
		FETCH FROM ReferencingFKs INTO @RefObjName, @FKeyName
		
		Print CASE WHEN @Enable = 0 THEN 'Disabling' ELSE 'Enabling' END + ' individual constraints pointing at: ' + @ObjectName + '.'
		WHILE(@@FETCH_STATUS = 0)
		BEGIN--WHILE
			---------------------------------------------------------------------------------

			Print CASE WHEN @Enable = 0 THEN 'Disabling' ELSE 'Enabling' END + ' constraint: ' + @FKeyName + '.'
			IF(@Enable = 1)
			BEGIN--IF
				SET @tSQL = 'ALTER TABLE ' + @RefObjName + ' WITH CHECK CHECK CONSTRAINT [' + @FKeyName + ']'
			END ELSE BEGIN--IF
				SET @tSQL = 'ALTER TABLE ' + @RefObjName + ' NOCHECK CONSTRAINT [' + @FKeyName + ']'
			END--IF

			EXEC(@tSQL)

			---------------------------------------------------------------------------------
			FETCH FROM ReferencingFKs INTO @RefObjName, @FKeyName
		END--WHILE

		CLOSE ReferencingFKs DEALLOCATE ReferencingFKs
	END--IF
END--PROCEDURE
GO