How to find all child tables that belong to a parent table

_{Wednesday, March 5, 2008 5:02 PM}

How do i find the child tables that belong to a parent table.  Thanks.

_{Wednesday, March 5, 2008 5:16 PM ✅Answered | 1 vote}

This should do it for TSQL so long as you enforce referential integrity.  Without that, you might try and do a column name search based on the column name of the PK of the base table?  That might help some.

Code Snippet

-- SQL 2005

select object_name(parent_object_id), object_name(referenced_object_id)

from sys.foreign_keys

WHERE object_name(referenced_object_id) ='TableName'

-- SQL 2000

select o1.name, o2.name

from sysforeignkeys fk

join sysobjects o1 on o1.id = fk.fkeyid

join sysobjects o2 on o2.id = fk.rkeyid

where o2.name = 'tablename'

_{Wednesday, March 5, 2008 5:19 PM ✅Answered}

Hopefully there are foreign key constrants defined?

Check out sys.foreign_keys and sys.foreign_key_columns

You can use OBJECT_NAME(SomeObjectID) to get the english names back instead of the object id's.  For instance:

Code Snippet

SELECT OBJECT_NAME(constraint_object_id) AS ConstraintName, OBJECT_NAME(parent_object_id) AS ReferencingObject,

OBJECT_NAME(referenced_object_id) AS ReferencedObject, *

FROM sys.foreign_key_columns

_{Wednesday, March 5, 2008 5:06 PM}

In SSMS, you can right click the table and View Dependencies.

_{Wednesday, March 5, 2008 5:07 PM}

You might be able to write some SQL that would get you most or maybe even all of the child tables, depending on the complexity of the database, but without knowledge of the database design and how the application works you may never find all the child tables just by looking and querying

_{Thursday, March 6, 2008 11:14 AM}

  Jonathan Kehayias Wrote:

This should do it for TSQL so long as you enforce referential integrity.  Without that, you might try and do a column name search based on the column name of the PK of the base table?  That might help some.

Code Snippet

-- SQL 2005

select object_name(parent_object_id), object_name(referenced_object_id)

from sys.foreign_keys

WHERE object_name(referenced_object_id) ='TableName'

-- SQL 2000

select o1.name, o2.name

from sysforeignkeys fk

join sysobjects o1 on o1.id = fk.fkeyid

join sysobjects o2 on o2.id = fk.rkeyid

where o2.name = 'tablename'

Thanks Jonathan for your response.:

I am using SQL 2005. These scripts can find the child tables of a parent table when there are foreign keys defined. But what method can i use if i need find the child tables if foreign keys are not defined on the tables. Thanks

_{Thursday, March 6, 2008 11:42 AM}

TheSQL Wrote:

Hopefully there are foreign key constrants defined?

Check out sys.foreign_keys and sys.foreign_key_columns

You can use OBJECT_NAME(SomeObjectID) to get the english names back instead of the object id's.  For instance:

Code Snippet

SELECT OBJECT_NAME(constraint_object_id) AS ConstraintName, OBJECT_NAME(parent_object_id) AS ReferencingObject,

OBJECT_NAME(referenced_object_id) AS ReferencedObject, *

FROM sys.foreign_key_columns

Thanks for your response TheSQL:

I am using SQL 2005. These script you have above can find the child tables of a parent table when there are foreign keys defined. But what method can i use if i need find the child tables if foreign keys are not defined on the tables. Thanks

_{Thursday, March 6, 2008 12:20 PM}

If they all happen to be named the exact same thing:

Code Snippet

select a.Table_Name +'.'+a.column_name, b.Table_Name +'.'+b.column_name

from Information_schema.columns a

join Information_schema.columns b ON a.column_name = b.column_name and a.Table_name != b.Table_Name

where a.table_name = 'tablename'

If not, then the best we can do is wish you luck.  There is no other method to do what you ask.  This is what FK relationships do.

_{Wednesday, December 23, 2009 5:47 AM}

Hi,

I found out this using CTE. May not be Best but i guess it might help.

This searches and find the Least Nth Level Child Table.

Say there is a MasterTable A and its has three child Tables B C D

Now Again these Childtable may act as Master to Someother Child Tables say B1 B2 C1 C2 D1 D1 where B1 B2 are child tables of B and So on.

In this this Script will provide you the Hirearchy Level as well.

DECLARE @MasterTableName AS VARCHAR(255)='YourMasterTableName'

;WITH ParChi AS

(

--initialization

SELECT object_name(referenced_object_id) as ParentTable, object_name(parent_object_id) as ChildTable, 1 as hlevel

FROM sys.foreign_keys

WHERE object_name(referenced_object_id) =@MasterTableName

UNION ALL

--recursive execution

SELECT object_name(e.referenced_object_id), object_name(e.parent_object_id),m.hlevel  + 1

FROM sys.foreign_keys e INNER JOIN ParChi m 

ON object_name(e.referenced_object_id) = m.ChildTable --.object_name(parent_object_id)

and not (m.ChildTable = object_name(e.parent_object_id) and m.ParentTable = object_name(e.referenced_object_id))Used this because Might Go in Infinite if some table is self Referenced

)

SELECT distinct childtable, hlevel FROM ParChi

order by hlevel desc

option (maxrecursion 32767)

If any Modifications or still fine tune to my above query if any betterment please suggest as i am still a learner.

Thanks,
Manjunath C Bhat