ViewTables.txt

WITH CTE_VIEW_TABLE_USAGE AS
(
	SELECT
		OBJECT_SCHEMA_NAME(sed.referencing_id) AS VIEW_SCHEMA,
		OBJECT_NAME(sed.referencing_id) AS VIEW_NAME,
		t.TABLE_SCHEMA,
		t.TABLE_NAME,
		t.TABLE_TYPE
	FROM
		sys.sql_expression_dependencies sed
	INNER JOIN
		INFORMATION_SCHEMA.TABLES t
	ON
		sed.referenced_schema_name = t.TABLE_SCHEMA
	AND
		sed.referenced_entity_name = t.TABLE_NAME
	WHERE
		sed.referencing_id <> sed.referenced_id

),CTE_ViewTables AS
(
	SELECT 
		1 AS Depth,
		vtu.VIEW_SCHEMA,
		vtu.VIEW_NAME,
		vtu.TABLE_SCHEMA,
		vtu.TABLE_NAME,
		vtu.TABLE_TYPE
	FROM
		CTE_VIEW_TABLE_USAGE vtu
	UNION ALL
	SELECT 
		Depth + 1 AS Depth,
		vt.VIEW_SCHEMA,
		vt.VIEW_NAME,
		vtu.TABLE_SCHEMA,
		vtu.TABLE_NAME,
		vtu.TABLE_TYPE
	FROM
		CTE_ViewTables vt
	INNER JOIN
		CTE_VIEW_TABLE_USAGE vtu
	ON
		vtu.VIEW_SCHEMA = vt.TABLE_SCHEMA
	AND
		vtu.VIEW_NAME = vt.TABLE_NAME
)
SELECT DISTINCT
	VIEW_SCHEMA,
	VIEW_NAME,
	MAX(Depth) AS Depth,
	TABLE_SCHEMA,
	TABLE_NAME
FROM
	CTE_ViewTables vt
WHERE
	vt.VIEW_SCHEMA = 'osl_c'
AND
	vt.TABLE_TYPE = 'BASE TABLE'
GROUP BY
	VIEW_SCHEMA,
	VIEW_NAME,
	TABLE_SCHEMA,
	TABLE_NAME
ORDER BY
	VIEW_SCHEMA, VIEW_NAME, TABLE_SCHEMA, TABLE_NAME