Archive for November, 2012

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'

Continue reading