Script to drop fk in all tables linked to a pk

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Script to drop fk in all tables linked to a pk

Author

Topic

sarahmfr
Posting Yak Master

214 Posts

Posted - 2012-02-07 : 12:59:29

is there a script to drop all fk in all tables linked to a pk in one table and then recreate them again
i.e.
Table A has pk sectionid
Table B,C,D,F has fk pointing to sectionid
is there a script that can drop all those foreign keys at once
and another script to recreate them after I recreate the pk

sarah

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-02-07 : 13:23:35

This finds all FKs on a table; not a PK.[CODE]-----------------------------------------------------------------------------
--- Given a table name, return all the Foreign Keys that reference it, their
--- columns and referenced table and columns.
-----------------------------------------------------------------------------

declare
@table sysname = 'MyTable',
@truncate bit = 0

-----------------------------------------------------------------------------

declare
@PKey int,
@MaxPkey int,
@fkName sysname,
@Cols varchar(1000),
@RefCols varchar(1000),

@OnDelete nvarchar(11),
@OnUpdate nvarchar(11),

@is_not_for_replication bit,
@is_disabled bit

declare @FKs table (
fkName sysname,
tblName sysname,
colName sysname,
column_id int,
refTblName sysname,
refColName sysname,

OnDelete nvarchar(11),
OnUpdate nvarchar(11),

is_not_for_replication tinyint,
is_disabled tinyint
)

declare @DropCreates table (
PKey int identity(1, 1),

fkName sysname,
tblName sysname,
refTblName sysname,

dropStmt varchar(500),
createStmt varchar(500)
)

-----------------------------------------------------------------------------
--- Get raw foreign key details
-----------------------------------------------------------------------------

insert into @FKs (
fkName,
tblName,
colName,
column_id,
refTblName,
refColName,
OnDelete,
OnUpdate,
is_not_for_replication,
is_disabled
)
select
fk.Name fkName,

parent.Name tblName,
fkcc.Name colName,
fkc.constraint_column_id,

reference.Name refTable,
refCol.Name refColumn,

case delete_referential_action
when 0 then N'NO ACTION'
when 1 then N'CASCADE'
when 2 then N'SET NULL'
when 3 then N'SET DEFAULT'
else N'<Unknown>'
end,

case update_referential_action
when 0 then N'NO ACTION'
when 1 then N'CASCADE'
when 2 then N'SET NULL'
when 3 then N'SET DEFAULT'
else N'<Unknown>'
end,

fk.is_not_for_replication,

fk.is_disabled
from
sys.foreign_keys fk
inner join
sys.foreign_key_columns fkc
on fkc.constraint_object_id = fk.object_id
inner join
sys.objects parent
on fk.parent_object_id = parent.object_id
inner join
sys.columns fkcc
on fkcc.object_id = parent.object_id
and fkcc.column_id = fkc.parent_column_id

inner join
sys.objects reference
on reference.object_id = fk.referenced_object_id

inner join
sys.columns refCol
on refCol.object_id = reference.object_id
and refCol.column_id = fkc.referenced_column_id
where
reference.Name = @table
-- reference.Name = 'AppConnDatabases'
order by
parent.Name,
fk.Name,
fkc.constraint_column_id

--/**/select * from @FKs

-----------------------------------------------------------------------------
--- Extract unique FKs
-----------------------------------------------------------------------------

insert into @DropCreates (
fkName,
tblName,
refTblName,
dropStmt,
createStmt
)
select distinct
fkName,
tblName,
refTblName,
'',
''
from
@FKs

select
@PKey = 1,
@MaxPKey = max(PKey)
from
@DropCreates

--/**/select * from @DropCreates

-----------------------------------------------------------------------------
--- Generate the DROP and CREATE statements
-----------------------------------------------------------------------------

while (@PKey <= @MaxPKey)
begin
select
@fkName = fkName
from
@DropCreates
where
PKey = @PKey

---------------------------------------------

select
@Cols = '',
@RefCols = ''

update @FKs
set @Cols += colName + ', ',
@RefCols += refColName + ', '
where
fkName = @fkName

select
@OnDelete = min(OnDelete),
@OnUpdate = min(OnUpdate),

@is_not_for_replication = min(is_not_for_replication),
@is_disabled = min(is_disabled)
from
@FKs
where
fkName = @fkName

---------------------------------------------

update @DropCreates
set DropStmt = 'ALTER TABLE dbo.[' + tblName + '] DROP CONSTRAINT [' + @fkName + '];',
CreateStmt = 'ALTER TABLE dbo.[' + tblName + '] ADD CONSTRAINT [' + @fkName + '] ' + char(13) + char(10) +
char(9) + 'FOREIGN KEY (' + left(@cols, len(@Cols) - 1) + ') ' + char(13) + char(10) +
char(9) + 'REFERENCES dbo.[' + refTblName + '] (' + left(@RefCols, len(@RefCols) - 1) + ') ' + char(13) + char(10) +
char(9) + 'ON DELETE ' + @OnDelete + char(13) + char(10) +
char(9) + 'ON UPDATE ' + @OnUpdate +
case
when @is_not_for_replication = 1
then char(13) + char(10) + char(9) + 'NOT FOR REPLICATION'
else ''
end +
';'
where
fkName = @fkName

---------------------------------------------

set @PKey += 1;
end

-----------------------------------------------------------------------------
--- Return the results
-----------------------------------------------------------------------------

select a.SqlStmt
from (
select 1 ord, DropStmt SqlStmt
from @DropCreates
--order by DropStmt

union all

select 2, ''

union all

select 3 ord, case @truncate
when 1 then 'truncate table dbo.' + @table + ';'
else ''
end SqlStmt

union all

select 4, ''

union all

select 5, CreateStmt SqlStmt
from @DropCreates
--order by DropStmt
)a
order by ord[/CODE]

=================================================
Men shout to avoid listening to one another. -Miguel de Unamuno