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)

J.D. · Answer 1 · 2023-01-11T02:23:13.267

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)

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

1 Answers1