I have this script to list all dependencies of all views in a database . I get the dependencies in the right order when I use sysdepends ,but I am missing a few dependencies while using sysdepends. When I use SYS.sql_expression_dependencies instead. it runs forever . I don't know what's wrong with the code. Some one please help.
;WITH dependencies
AS (
select distinct procs.NAME AS ViewName ,OBJDEP.NAME as Depends ,sch.name as schemaname
FROM TEMPLATE_TEST.SYS.sql_expression_dependencies a
INNER JOIN TEMPLATE_TEST.sys.objects OBJ ON a.referencing_id = OBJ.OBJECT_ID
INNER JOIN TEMPLATE_TEST.sys.objects OBJDEP ON a.referenced_id = OBJDEP.OBJECT_ID
inner join TEMPLATE_TEST.sys.views procs on a.referencing_id = procs.object_id
inner join TEMPLATE_TEST.sys.schemas sch on sch.schema_id=procs.schema_id
where OBJDEP.type='V' AND OBJ.type='V'
),
no_dependencies
AS (
SELECT obj.NAME AS ViewName,sch.name as schemaname
FROM TEMPLATE_TEST.sys.objects OBJ
inner join TEMPLATE_TEST.sys.schemas sch on sch.schema_id=obj.schema_id
WHERE NOT exists (SELECT 1 FROM dependencies k where obj.name=k.ViewName and sch.name=k.schemaname)
AND OBJ.type in('v')
),
recursiv
AS (
SELECT ViewName AS [SP]
, CAST('' AS VARCHAR(max)) AS DependsON
, 0 AS LVL
,schemaname
FROM no_dependencies
UNION ALL
SELECT d.ViewName 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 DISTINCT * FROM recursiv ORDER BY LVL