How to find child and grandchildren tables of a main table by order

Question

I have this script to return all child and grandchildren tables with their level from a database . How do I change this if I only want to return the child and grandchildren tables of a particular table .

;WITH dependencies -- Get sp with  dependencies
AS (
    SELECT 
        DISTINCT procs.NAME AS Tablename 
                   ,OBJDEP.NAME as Depends 
                   ,sch.name as schemaname
    FROM    sysforeignkeys a
        INNER JOIN sysobjects OBJ ON a.fkeyid = OBJ.id
        INNER JOIN sysobjects OBJDEP ON a.rkeyid = OBJDEP.id
        inner join   sys.objects procs  on a.fkeyid = procs.object_id
        inner join sys.schemas sch on sch.schema_id=procs.schema_id
    where obj.type='U'
    and OBJDEP.type='u'
    ), 
no_dependencies 
AS (
    SELECT   
        obj.NAME AS Tablename
        ,sch.name as schemaname
     FROM sys.objects OBJ
     INNER JOIN sys.schemas sch on sch.schema_id=obj.schema_id
     WHERE  NOT EXISTS (
        SELECT 1 
        FROM dependencies k 
        where obj.name=k.Tablename 
        and sch.name=k.schemaname) and type='u'
    
    ), 
recursiv -- recursive CTE to get dependencies
AS (
    SELECT Tablename AS [SP]
        , CAST('' AS VARCHAR(max)) AS DependsON
        , 0 AS LVL 
        ,schemaname
    FROM no_dependencies
    UNION ALL
    SELECT d.Tablename AS [SP]
         ,CAST( d.Depends AS VARCHAR(max))
         ,R.lvl + 1 AS LVL
         ,d.schemaname
    FROM dependencies d
    INNER JOIN recursiv r
        ON d.Depends = r.[SP]
    )


SELECT * FROM RECURSIV  ORDER BY LVL  option (maxrecursion 0)

Answer 1

You need to pass the root parent (from level 0) item through every iteration of the recursion. You can do that by having a dedicated RootParent field which you continue to pass the same value for all the way down the recursion, like so:

;WITH dependencies -- Get sp with  dependencies
AS (
    SELECT 
        DISTINCT procs.NAME AS Tablename 
                   ,OBJDEP.NAME as Depends 
                   ,sch.name as schemaname
    FROM    sysforeignkeys a
        INNER JOIN sysobjects OBJ ON a.fkeyid = OBJ.id
        INNER JOIN sysobjects OBJDEP ON a.rkeyid = OBJDEP.id
        inner join   sys.objects procs  on a.fkeyid = procs.object_id
        inner join sys.schemas sch on sch.schema_id=procs.schema_id
    where obj.type='U'
    and OBJDEP.type='u'
    ), 
no_dependencies 
AS (
    SELECT   
        obj.NAME AS Tablename
        ,sch.name as schemaname
     FROM sys.objects OBJ
     INNER JOIN sys.schemas sch on sch.schema_id=obj.schema_id
     WHERE  NOT EXISTS (
        SELECT 1 
        FROM dependencies k 
        where obj.name=k.Tablename 
        and sch.name=k.schemaname) and type='u'
    
    ), 
recursiv -- recursive CTE to get dependencies
AS (
    SELECT 
         Tablename AS RootParent,
         schemaname AS RootParentSchema,
         Tablename AS [SP]
        , CAST('' AS VARCHAR(max)) AS DependsON
        , 0 AS LVL 
        ,schemaname
    FROM no_dependencies
    UNION ALL
    SELECT 
         R.RootParent,
         R.RootParentSchema,
         d.Tablename AS [SP]
         ,CAST( d.Depends AS VARCHAR(max))
         ,R.lvl + 1 AS LVL
         ,d.schemaname
    FROM dependencies d
    INNER JOIN recursiv r
        ON d.Depends = r.[SP]
    )


SELECT * 
FROM RECURSIV
WHERE RootParent = 'TheTableName'
    --AND RootParentSchema = 'TheSchemaName' -- Optionally you'll want to filter by a specific schema too if you have different objects with the same name in different schemas. If so, you can uncomment this AND predicate.
ORDER BY LVL
option (maxrecursion 0)