Category Archives: Disable and Enable all the Foreign Keys in Database

Disable and Enable Foreign Keys on all Tables in a database


—————— One Method ——————————-

before dropping replace the DROP with create, so that you can put the FK’s back

 — change the o/p to text mode

use [dbname]

 go

SELECT ‘alter table ‘+user_name(uid)+’.’+object_name(parent_obj)+’ DROP CONSTRAINT ‘+name,char(13)+char(10)+’go’ from sysobjects where xtype = ‘F’

go

_________________________________________________________________________________________

———————– Second Method ————————————–

 

——1) Generate Add FK Statements.

SET NOCOUNT ON

—Create table to insert the proc values

IF OBJECT_ID(‘dbo.fkeys’, ‘U’) IS NOT NULL
DROP TABLE dbo.fkeys
go
CREATE TABLE dbo.fkeys (c1 varchar(8000))
GO

——- Generate Adds for All Foreign Keys in Database

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), @fline varchar(8000)
set @fline = ”
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)
set @fline=@fline + @pline
INSERT INTO fkeys VALUES(@pline)

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

———2) DROP FK’s

DECLARE @LINE VARCHAR(MAX)
DECLARE fkCursor CURSOR FOR
select distinct ‘ALTER TABLE [dbo].[‘ + object_name(fkeyid) +
‘] DROP CONSTRAINT ‘ + object_name(constid) +
CHAR(13) + CHAR(10) + ‘ ‘
from sysforeignkeys

OPEN fkCursor

FETCH NEXT FROM fkCursor
INTO @LINE

WHILE @@FETCH_STATUS = 0
BEGIN
–PRINT @LINE
EXEC (@LINE)

FETCH NEXT FROM fkCursor
INTO @LINE
END

CLOSE fkCursor
DEALLOCATE fkCursor

3) EXEX generate add fk’s

—————- exec the generated add fk statements

DECLARE @sql varchar(8000), @stmts varchar(8000)
–set @sql = ”
DECLARE fkCursor CURSOR FOR
select * from fkeys

OPEN fkCursor

FETCH NEXT FROM fkCursor
INTO @stmts

WHILE @@FETCH_STATUS = 0
BEGIN
–print @stmts
exec (@stmts)
FETCH NEXT FROM fkCursor
INTO @stmts
END
CLOSE fkCursor
DEALLOCATE fkCursor

 

%d bloggers like this: