Is it possible to list VIEWS being used inside another VIEW?-CodePudding

In SQLServer, I am able to list the tables used by a VIEW with INFORMATION_SCHEMA.VIEW_TABLE_USAGE. But some of my VIEWS use just other VIEWS. Is there a way to get that list of VIEW names?

CodePudding user response：

You can use sys.dm_sql_referenced_entities to find all references, then join back to sys.views to get the child view names

SELECT
  r.referenced_entity_name,
  r.referenced_minor_name,
  v2.name
FROM sys.views v
JOIN sys.schemas s ON s.schema_id = v.schema_id
CROSS APPLY sys.dm_sql_referenced_entities(s.name   '.'   v.name, 'OBJECT') r
JOIN sys.views v2 ON v2.object_id = r.referenced_id;

If you were looking only for views which only refer to other views, you can do something like this

SELECT *
FROM (
    SELECT
      r.referenced_entity_name,
      r.referenced_minor_name,
      v2.name,
      nonViews = COUNT(CASE WHEN o2.type IN ('FT','IF','U','TF') THEN 1 END)
                   OVER (PARTITION BY v.object_id)
    FROM sys.views v
    JOIN sys.schemas s ON s.schema_id = v.schema_id
    CROSS APPLY sys.dm_sql_referenced_entities(s.name   '.'   v.name, 'OBJECT') r
    JOIN sys.objects o2 ON o2.object_id = r.referenced_id
    LEFT JOIN sys.views v2 ON v2.object_id = r.referenced_id
) v
WHERE nonViews = 0;

Generally you should avoid INFORMATION_SCHEMA, it's only for compatibility.