SQL/Dependencies.sql at master · cjfaulkner/SQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
/****************************************************************************************************************************************************
	Title : Dependecies

	Description:
		Which view is called from another view

	Change History:
		Date		Author          Version	Description
		----------	--------------- -------	------------------------------------
		2019-??-??	Chris Faulkner	1.00	Created

****************************************************************************************************************************************************/
WITH CTE_RefChain AS
(
	SELECT
		object_name(referenced_id) AS ViewName,
		object_name(referencing_id) AS ReferencedFrom
	FROM
		sys.sql_expression_dependencies
	UNION ALL
	SELECT
		object_name(referenced_id) AS ViewName,
		object_name(referencing_id) AS ReferencedFrom
	FROM
		sys.sql_expression_dependencies
	INNER JOIN
		CTE_RefChain
	ON
		object_name(referenced_id) = ReferencedFrom
)
SELECT DISTINCT * FROM CTE_RefChain
ORDER BY 1