Unconstrain/Reconstrain a table

CREATE PROCEDURE dbo.utl_UnConstrain 
	@cTableName CHAR(30) OUTPUT
	AS
/********************************************************************************************************************************************************
Purpose: 	Report Constraint Drops and Creates for a given table so work can be carried out on data

Amendment Log
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Date 		Who			Comment
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
23/04/01	JHAYNE		Initial Version (me@julianhaynes.freeserve.co.uk)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
********************************************************************************************************************************************************/
DECLARE
	@nKeyNo		INT
	,@cLastAlter		CHAR(255)
	,@cAlter		CHAR(255)
	,@cFC			CHAR(30)
	,@cLastRef		CHAR(255)
	,@cRef			CHAR(255)
	,@cRC			CHAR(30)
	,@nLastConstID		INT
	,@nConstID		INT
	,@cFCColumns		VARCHAR(1000)
	,@cRCColumns		VARCHAR(1000)
	,@cSQLCreate		VARCHAR(4000)
	
--Initialise
SET NOCOUNT ON

--Output Drops
SELECT 
	'ALTER TABLE ' + RTRIM(OBJECT_NAME(fKeyID)) + ' DROP CONSTRAINT ' + RTRIM(OBJECT_NAME(ConstID)) AS '--SQL to drop constraints'
FROM
	SysForeignKeys
WHERE
	rKeyID = OBJECT_ID(@cTableName)
	OR fKeyID = OBJECT_ID(@cTableName)
GROUP BY
	ConstID
	,fKeyID
	
--Output message 1
SELECT '--Do your work here and don''t forget to reapply constraints below afterwards!
--You cannot rerun this proc to get below once Constraints Dropped so don''t lose these results - use a different Query window from now on!
'

--Output message 2
SELECT '--Tip: If the output is being truncated then go into SQL Query Analyser select Query, Current Connection Options..., Advanced Tab, Maximum charactors per column and changed from 256 to 1024
'

--------------------------------------------------------------------------------
--Output Creates
--------------------------------------------------------------------------------

--Table to temporarily store results
CREATE TABLE #PDMFKC (cSQLCreate VARCHAR(4000) NULL)

--Declare Cursor
DECLARE Constraints CURSOR FOR
SELECT
	FK.keyNo AS nKeyNo
	,'ALTER TABLE '
		+ RTRIM(OBJECT_NAME(FK.fKeyID))
		+ ' ADD CONSTRAINT '
		+ RTRIM(OBJECT_NAME(FK.ConstID))
		+ ' FOREIGN KEY' AS cAlter
	,LEFT(COL_NAME(FK.fKeyID, FK.fKey), 30) AS cFC 
	,'REFERENCES '
		+ RTRIM(OBJECT_NAME(FK.rKeyID)) AS cRef
	,LEFT(COL_NAME(FK.rKeyID, FK.rKey), 30) AS cRC
	,FK.ConstID AS nConstID
FROM 
	SysForeignKeys FK
WHERE
	rKeyID = OBJECT_ID(@cTableName)
	OR fKeyID = OBJECT_ID(@cTableName)
ORDER BY
	FK.ConstID
	,FK.keyNo	

--Open Cursor
OPEN Constraints

--Fetch First Row
FETCH NEXT FROM Constraints INTO 
	@nKeyNo
	,@cLastAlter
	,@cFC
	,@cLastRef
	,@cRC
	,@nLastConstID

--Loop through rows and check constraints for each one
SELECT @cFCColumns = ''
SELECT @cRCColumns = ''
WHILE @@FETCH_STATUS = 0
	BEGIN

		--Build columns data
		SELECT @cFCColumns = RTRIM(@cFCColumns) + RTRIM(@cFC) + ','
		SELECT @cRCColumns = RTRIM(@cRCColumns) + RTRIM(@cRC) + ','

		--Fetch Next Row
		FETCH NEXT FROM Constraints INTO 
			@nKeyNo
			,@cAlter
			,@cFC
			,@cRef
			,@cRC
			,@nConstID
		
		--Check for fetch failure
		IF @@FETCH_STATUS = -2
			BEGIN
				SELECT 'Fetch failed'
				RETURN
			END
		
		--Check for change in constraint
		IF @nLastConstID <> ISNULL(@nConstID, -1) OR @@FETCH_STATUS = -1
			BEGIN
				--Ouput
				BEGIN
					SELECT @cFCColumns = LEFT(@cFCColumns, LEN(@cFCColumns) -1)
					SELECT @cRCColumns = LEFT(@cRCColumns, LEN(@cRCColumns) -1)
					INSERT #PDMFKC SELECT RTRIM(@cLastAlter) + ' (' + RTRIM(@cFCColumns) + ') ' + RTRIM(@cLastRef) +  ' (' + RTRIM(@cRCColumns) + ') '
				END

				--Reset vars
				SELECT @nLastConstID = @nConstID
				SELECT @cLastAlter = @cAlter
				SELECT @cLastRef = @cRef
				SELECT @cFCColumns = ''
				SELECT @cRCColumns = ''
			END

	END

--Final Output
SELECT cSQLCreate AS '--SQL to create constraints' FROM #PDMFKC 

--Tidy up
CLOSE Constraints
DEALLOCATE Constraints
DROP TABLE #PDMFKC

--End Proc
SET NOCOUNT OFF
RETURN