SQL to find out and disable child tables referencing parent table in SQL Server

You can use this query to find out child tables referencing parent Table.

 
--replace 't_User'with your table name
--query to find out which table have references
select
  so.name 'foreign key name',
  OBJECT_NAME(parent_obj) 'table',
  OBJECT_NAME(sf.fkeyid) 'referencing table',
  sc1.name 'referencing column',
  OBJECT_NAME(sf.rkeyid) 'referenced table', 
  sc2.name 'referenced column'
from sysobjects so
inner join sysforeignkeys sf on so.id = sf.constid
inner join syscolumns sc1 on sf.fkeyid = sc1.id and sf.fkey = sc1.colid
inner join syscolumns sc2 on sf.rkeyid = sc2.id and sf.fkey = sc2.colid
where so.xtype in ('F','PK')
and OBJECT_NAME(sf.rkeyid) = 't_User'


Use those following queries to enable/disable referential integrity.

 
--disable all constraints refrencing a parent table
alter table t_problemLog nocheck constraint all; 

--enable all constraints refrencing a parent table
alter table t_problemLog check constraint all;


However, this following one is most efficient one, you can generate SQL Script using this and use it enable/disable referential in Child Tables.


--SQL to genarate script to disable constraints refrencing a parent table
select distinct 'alter table '+ OBJECT_NAME(fkeyid)+ ' nocheck constraint all;' 
from sysforeignkeys where OBJECT_NAME(rkeyid) = 't_User'

--Output
alter table t_problemLog nocheck constraint all; 
--execute the output of this query and disable constraints.


Thanks
A Rahim Khan

Advertisements
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: