Tuesday, March 30, 2010

SQL Script to find all Child table upto Nth Level in SQL Server

I found out this using CTE(Common Table Expression). May not be the best but might help guess. 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'
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
--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 YOURCTENAME 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 YOURCTENAME
order by hlevel desc
option (maxrecursion 32767);

No comments:

Post a Comment