Object Dependency

• nadersam

  SSCertifiable

  Points: 6542

  More actions

  October 3, 2017 at 12:26 am

  #1961991

  John Mitchell-245523 - Monday, October 2, 2017 7:20 AM

  Nader

  Difficult when I can't see what's in your database.  But try changing referencing_id to referenced_id in the join predicate of the anchor part.  I recommend that you arrange it so that you return the name of the referencing and referenced objects in the same row - that'll make it a lot easier to understand the results.

  John

  Thank you John, i made another union in the anchor part for that to be like following.

  WITH CTE_Dep
  as
  (
  SELECT sys.objects.object_id, sys.sql_expression_dependencies.referenced_id,sys.objects.name, 1 AS SortIndex,111 as 'anchor'
  FROM sys.sql_expression_dependencies inner JOIN
  sys.objects ON sys.sql_expression_dependencies.referencing_id = sys.objects.object_id and referenced_id not in (select OBJECT_ID from sys.objects where type ='u')
  union
  SELECT sys.objects.object_id, sys.sql_expression_dependencies.referencing_id,sys.objects.name, 10 AS SortIndex,888 as 'anchor'
  FROM sys.sql_expression_dependencies inner JOIN
  sys.objects ON sys.sql_expression_dependencies.referenced_id = sys.objects.object_id and referenced_id not in (select OBJECT_ID from sys.objects where type ='u')
  union all
  select dep.referencing_id,dep.referenced_id,obj.name, (SortIndex + 1 ) as SortIndex ,222 as 'recursive'
  from CTE_Dep inner join
  sys.sql_expression_dependencies dep
  on CTE_Dep.referenced_id = dep.referencing_id
  inner JOIN sys.objects obj
  ON obj.object_id = dep.referencing_id
  WHERE (type_desc <> 'USER_TABLE')
  )
  select distinct name ,max(SortIndex) as SortIndex from CTE_Dep group by name
  order by NAME desc

  Regards
  Nader