By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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
union all
select s.vw, s.obj, s.objt
from sample s
join cte c
on c.vw = s.obj
)
select * from cte
| vw | obj | objt |
|---|---|---|
| V1 | Saphire | Table |
| V2 | V1 | View |
| V3 | V2 | View |