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
