Stuck on getting deps through a query

I have a table, called refs, which has columns such as:

depname, refname, reftype.

Where, refname is the name of the table dependent on a view.

For this use case, I want to get all the views directly or indirectly dependent on table(s) containing ‘sapphire’ in their name.

I’m not sure how to move ahead, and link the indirectly dependencies to views that indirectly depend on it. Any help would be really appreciated!

Answer

In a few words, and using sql server (you didn’t tag your rdbms), you can do it like this:

declare @objName varchar(100) = 'Saphire'

;with sample as (
  select 'V1' as vw, 'Saphire' as obj, 'Table' as objt union all
  select 'V2' as vw, 'V1' as obj, 'View' as objt union all
  select 'V3' as vw, 'V2' as obj, 'View' as objt
),
cte as (
  select vw, obj, objt from sample where obj = @objName -- anchor member
  union all
  -- recursive member
  select s.vw, s.obj, s.objt
  from sample s
    join cte c
      on c.vw = s.obj
)
select * from cte

You can test on this db<>fiddle

CTE behaves in a very particular way, compared with the behaviour of usual sql queries. CTE uses an anchor member and a recursive member within the CTE definition. It helps get hierarchies within a table.