Nov
22
2008

Script to drop and re-create all foreign keys

An article by ehosca No Comments 

------------
-- 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 ['+ SCHEMA_NAME(CAST(OBJECTPROPERTYEX(tbl.id , 'SchemaId') AS INT)) +'].[' + object_name(fkeyid) + 

'] DROP CONSTRAINT ' + object_name(constid) + 

CHAR(13) + CHAR(10) + 'go' 

from sysforeignkeys 

JOIN sysobjects ON sysobjects.id = constid 
JOIN sysobjects tbl ON tbl.id = fkeyid 

JOIN syscolumns col ON col.id = fkeyid AND col.colid = fkey 
JOIN sysobjects ltbl ON ltbl.id = rkeyid 

JOIN syscolumns lcol ON lcol.id = rkeyid AND lcol.colid = rkey 


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),@schema 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
     SCHEMA_NAME(CAST(OBJECTPROPERTYEX(tbl.id , 'SchemaId') AS INT)),
     sysobjects.name,
     tbl.name,
     ltbl.name,
    OBJECTPROPERTY ( constid , 'CnstIsDeleteCascade' ),
    OBJECTPROPERTY ( constid , 'CnstIsUpdateCascade' )
    FROM
     sysforeignkeys
     JOIN sysobjects ON sysobjects.id = constid
     JOIN sysobjects tbl ON tbl.id = fkeyid
     JOIN syscolumns col ON col.id = fkeyid AND col.colid = fkey
     JOIN sysobjects ltbl ON ltbl.id = rkeyid
     JOIN syscolumns lcol ON lcol.id = rkeyid AND lcol.colid = rkey    
    ORDER BY sysobjects.name    

OPEN fkCursor

FETCH NEXT FROM fkCursor 
    INTO @schema, @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 [' + @schema + '].[' + @tabName + '] ADD CONSTRAINT [' + @fkName + ']' +
    CHAR(13) + CHAR(10) + '   FOREIGN KEY (' + @fkCol + ') REFERENCES [' + @schema + '].[' + @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 @schema, @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

Month List