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)

Share

Share

Rate

5 (9)