Recursive CTE doesn't work properly

Hi experts,

My first attempt at a recursive CTE...it runs OK but every level is 0, but if I run the anchor part only I get fewer rows returned, so there should be higher levels present.

Where am I going wrong?

WITH VIEWS_CTE (depender_name, depender_type, dependee_name, dependee_type, object_level) AS
---- Anchor member definition
  (
  SELECT DISTINCT o.name, o.type_desc, p.name, p.type_desc, 0 as object_level
  FROM sys.sql_dependencies d
  INNER JOIN sys.objects o
      ON d.object_id = o.object_id
  INNER JOIN sys.objects p
      ON d.referenced_major_id = p.object_id
  WHERE o.type_desc <> 'SQL_TRIGGER'
    AND p.type_desc = 'VIEW'
    AND (o.name NOT LIKE 'MSMerge%' AND p.name NOT LIKE 'MSMerge%')
    AND p.name NOT IN
        (
        SELECT o.name
        FROM sys.sql_dependencies d
        INNER JOIN sys.objects o
            ON d.object_id = o.object_id
        INNER JOIN sys.objects p
            ON d.referenced_major_id = p.object_id
        WHERE o.type_desc <> 'SQL_TRIGGER'
          AND p.type_desc = 'VIEW'          
          AND (o.name NOT LIKE 'MSMerge%' AND p.name NOT LIKE 'MSMerge%')
        )
  UNION ALL
---- Recursive member definition
  SELECT o.name, o.type_desc, p.name, p.type_desc, object_level + 1
  FROM sys.sql_dependencies d
  INNER JOIN sys.objects o
      ON d.object_id = o.object_id
  INNER JOIN sys.objects p
      ON d.referenced_major_id = p.object_id
  INNER JOIN VIEWS_CTE vcte
      ON o.name = vcte.dependee_name
  WHERE o.type_desc <> 'SQL_TRIGGER'
    AND p.type_desc = 'VIEW'
    AND (o.name NOT LIKE 'MSMerge%' AND p.name NOT LIKE 'MSMerge%')
  )
---- Statement that executes the CTE    
  SELECT depender_name, depender_type, dependee_name, dependee_type, object_level
  FROM VIEWS_CTE
  ORDER BY depender_type, depender_name
sqlobjects.xlsx
LVL 3
colinspursAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brendt HessSenior DBACommented:
Can you show the list that would be output when only the anchor portion is run as well?

Thanks!
0
colinspursAuthor Commented:
Sorry for the delay in replying...

Hmm...the same set, so there is a different problem...
0
colinspursAuthor Commented:
Commenting out the "NOT IN" clause in the anchor gives the attached result set.  This clause is meant to identify bottom level, ie where the dependee object is not found to depend on anything.  I thought it may be the issue so commented it out and got these unpredictable results.
hierarchy-EE08-recursive-CTE-unp.sql
sqlobjects2.xlsx
0
colinspursAuthor Commented:
I solved it eventually...

if exists (select * from sysobjects where name='CB_OBJECT_HIERARCHY' and xtype='U')
    DROP TABLE CB_OBJECT_HIERARCHY
go

;WITH VIEWS_CTE (depender_name, depender_type, dependee_name, dependee_type, object_level) AS
---- Anchor member definition
  (

   SELECT DISTINCT o.name as oname, o.type_desc as otype_desc, p.name as pname, p.type_desc as ptype_desc, 0 as object_level

        FROM sys.sql_dependencies d1
        INNER JOIN sys.objects o
              ON d1.object_id = o.object_id
        INNER JOIN sys.objects p
              ON d1.referenced_major_id = p.object_id
        LEFT JOIN sys.sql_dependencies d2
              ON o.object_id = d2.referenced_major_id
--        WHERE o.type_desc = 'VIEW'
--            AND p.type_desc = 'VIEW'
        WHERE o.type IN ('V', 'FN', 'P', 'IF', 'TF')
            AND p.type IN ('V', 'FN', 'P', 'IF', 'TF', 'U')
            AND d2.referenced_major_id IS NULL
            AND o.name NOT LIKE 'MSMerge%'
            AND p.name NOT LIKE 'MSMerge%'
 
      UNION ALL
        
        SELECT o.name, o.type_desc, p.name, p.type_desc, object_level +1
        FROM sys.sql_dependencies d1
        INNER JOIN sys.objects o
              ON d1.object_id = o.object_id
        INNER JOIN sys.objects p
              ON d1.referenced_major_id = p.object_id
          INNER JOIN VIEWS_CTE vcte
                ON o.name = vcte.dependee_name
--        WHERE o.type_desc = 'VIEW'
--            AND p.type_desc = 'VIEW'
        WHERE o.type IN ('V', 'FN', 'P', 'IF', 'TF')
            AND p.type IN ('V', 'FN', 'P', 'IF', 'TF', 'U')
            AND o.name NOT LIKE 'MSMerge%'
            AND p.name NOT LIKE 'MSMerge%'
  )
 
SELECT DISTINCT depender_name, depender_type, dependee_name, dependee_type, object_level
INTO CB_OBJECT_HIERARCHY
FROM VIEWS_CTE
ORDER BY object_level, depender_name, dependee_name
OPTION (MAXRECURSION 1000)
GO
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
colinspursAuthor Commented:
I worked it out in the end, I was getting confused between the parent and child tables.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.