Technical Article

Drop and Add Foreign Keys

,

Have you tried using DTS to load a database and those pesky foreign keys mess you up? This is actually two scripts, one to generate the SQL needed to drop all the Foreign Keys in a database and another to Add the Foreign Keys back when you are done. I've thrown in Disabling and Enabling Triggers as a bonus.

Run the scripts in Query Analyzer with 'Results to text'. Once either script runs, save the results in a .sql file.

Assumption: all objects are owned by dbo

-----------------------------------------------------------
-- Generate Drop Disable.sql

SET NOCOUNT ON

print 'USE ' + DB_NAME() 
print ''

-- Generate Drops for All Foreign Keys in Database
print '-- Drop Foreign Keys'
print ''

select distinct 'ALTER TABLE [dbo].[' + object_name(fkeyid) +
'] DROP CONSTRAINT ' + object_name(constid) +
CHAR(13) + CHAR(10) + 'go'
from sysforeignkeys 
go

-- Generate Disable all triggers
print ''
print '-- Disable Triggers'
print ''

select distinct 'ALTER TABLE [dbo].[' + OBJECT_NAME(parent_obj) +
'] DISABLE TRIGGER ALL ' + 
CHAR(13) + CHAR(10) + 'go'
FROM sysobjects
WHERE OBJECTPROPERTY(id, 'IsTrigger') = 1 and parent_obj in
(select id from sysobjects where xtype = 'U')
go

-----------------------------------------------------------
-- Generate Add Enable.sql

SET NOCOUNT ON

print 'USE ' + DB_NAME() 
print ''

-- Generate Adds for All Foreign Keys in Database
print '-- Add Foreign Keys'
print ''

DECLARE @fkName varchar(800), @tabName varchar(800), @refName varchar(800)
DECLARE @isDel int, @isUpd int, @fkCol varchar(8000), @refCol varchar(8000)
DECLARE @pline varchar(8000)

DECLARE fkCursor CURSOR FOR
select distinct object_name(constid), object_name(fkeyid), 
object_name(rkeyid), 
OBJECTPROPERTY ( constid , 'CnstIsDeleteCascade' ),
OBJECTPROPERTY ( constid , 'CnstIsUpdateCascade' )
from sysforeignkeys k 
order by object_name(fkeyid)

OPEN fkCursor

FETCH NEXT FROM fkCursor 
INTO @fkName, @tabName, @refName, @isDel, @isUpd

WHILE @@FETCH_STATUS = 0
BEGIN
select @fkCol = NULL
SELECT @fkCol = ISNULL(@fkCol + ', ','') + '[' + col_name(fkeyid, fkey) + ']'
from sysforeignkeys 
where object_name(constid) = @fkName 
order by keyno

select @refCol = NULL
SELECT @refCol = ISNULL(@refCol + ', ','') + '[' + col_name(rkeyid, rkey) + ']'
from sysforeignkeys 
where object_name(constid) = @fkName 
order by keyno

select @pline = 'ALTER TABLE [dbo].[' + @tabName +
'] ADD CONSTRAINT [' + @fkName + ']' +
CHAR(13) + CHAR(10) + 
'   FOREIGN KEY (' + @fkCol + ') REFERENCES [dbo].[' + @refName +
'] (' + @refCol + ')'
if @isDel = 1 
select @pline = @pline + CHAR(13) + CHAR(10) + 
'     ON DELETE CASCADE'
if @isUpd = 1 
select @pline = @pline + CHAR(13) + CHAR(10) + 
'     ON UPDATE CASCADE'
select @pline = @pline + CHAR(13) + CHAR(10) + 'go'
print @pline
FETCH NEXT FROM fkCursor 
INTO @fkName, @tabName, @refName, @isDel, @isUpd
END

CLOSE fkCursor
DEALLOCATE fkCursor
GO

-- Generate Enable all triggers
print ''
print '-- Enable Triggers'
print ''

select distinct 'ALTER TABLE [dbo].[' + OBJECT_NAME(parent_obj) +
'] ENABLE TRIGGER ALL ' + 
CHAR(13) + CHAR(10) + 'go'
FROM sysobjects
WHERE OBJECTPROPERTY(id, 'IsTrigger') = 1 and parent_obj in
(select id from sysobjects where xtype = 'U')
go

Rate

5 (9)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (9)

You rated this post out of 5. Change rating