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'
;WITH  YOURCTENAME 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 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);