2

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
8
  • Can you provide the ddl for the objects in TEMPLATE_TEST? And tell which ones you having the problems with?
    – Peter
    Commented Dec 6, 2022 at 12:47
  • Hi @Peter , i am not sure what you asked. I am only selecting views from the database template_Test . Commented Dec 6, 2022 at 12:55
  • 1
    Yes, but to test your script, if you can delivery some scripts to create that database, with tables and views, would make life more easy :)
    – Peter
    Commented Dec 6, 2022 at 13:10
  • You can use this script on any database . You can just replace the database with your database. I know it's better with the same data,but I cant export data ,it's secured🙂 Commented Dec 6, 2022 at 14:05
  • Well, not really, as when I create a database with a table and 3 views which depend on each other. The query returns the right results. Can you provide an example which does not return the right results? It does not have to contain data, but at least ddl scripts where this is reproducable.
    – Peter
    Commented Dec 6, 2022 at 14:59

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.