Posts Tagged ‘ Child Tables ’

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

Advertisements