Unconstrain/Reconstrain a table

,

This script provides the DDL required to unconstrain and then reconstrain a table so that in between you can modify the tables contents without being hampered by referential integrity constraints a long the way.

Typical call:- utl_UnConstrain 'MyTable'

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 WhoComment
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
23/04/01JHAYNEInitial Version (me@julianhaynes.freeserve.co.uk)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
********************************************************************************************************************************************************/DECLARE
@nKeyNoINT
,@cLastAlterCHAR(255)
,@cAlterCHAR(255)
,@cFCCHAR(30)
,@cLastRefCHAR(255)
,@cRefCHAR(255)
,@cRCCHAR(30)
,@nLastConstIDINT
,@nConstIDINT
,@cFCColumnsVARCHAR(1000)
,@cRCColumnsVARCHAR(1000)
,@cSQLCreateVARCHAR(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

Rate

Share

Share

Rate