Find nested references

NestedReferences.sql

; WITH allReferences
AS
(
	SELECT 
		dsre.referenced_id AS Id
		, 0 AS lvl
		, CAST('.' + CAST(dsre.referenced_id AS VARCHAR(MAX)) + '.' AS VARCHAR(max)) AS path
		, CAST(CAST(1 AS BINARY(2)) AS VARBINARY(MAX)) AS sorthPath
		, 0 AS cycle
	FROM sys.dm_sql_referenced_entities('SEARCH.OBJECT', 'Object') dsre  /* <--- insert the object in a 'schema.object' format */
	WHERE dsre.referenced_id IS NOT NULL
	GROUP BY dsre.referenced_id
	UNION ALL
	SELECT
		dsre.referenced_id AS Id
		, ar.lvl + 1
		, CONCAT(ar.path, CAST(dsre.referenced_id AS VARCHAR(MAX)), '.') 
		, ar.sorthPath + CAST(ROW_NUMBER() OVER (PARTITION BY dsre.referenced_id ORDER BY (SELECT @@SPID)) AS BINARY(2))
		, IIF(ar.path LIKE '%.' + CAST(dsre.referenced_id AS VARCHAR(10)) + '.%', 1, 0) AS cycle
	FROM allReferences ar
	CROSS APPLY sys.dm_sql_referenced_entities(CONCAT(OBJECT_SCHEMA_NAME(ar.Id), '.', OBJECT_NAME(ar.Id)), 'Object') dsre 
	WHERE dsre.referenced_id IS NOT NULL
	AND ar.cycle = 0
)
SELECT 
	ar.Id
  , CONCAT(SCHEMA_NAME(o.schema_id), '.', o.name) AS fullName
  , o.name AS objectName
  , o.type_desc
  , ar.lvl
  , ar.path
  , MAX(ar.sorthPath)
FROM allReferences ar
JOIN sys.objects o ON ar.Id = o.object_id
WHERE 
	ar.cycle = 0
GROUP BY 
	ar.Id
  , CONCAT(SCHEMA_NAME(o.schema_id), '.', o.name)
  , o.name
  , o.type_desc
  , ar.lvl
  , ar.path
ORDER BY MAX(ar.sorthPath)