Find all tables that are related to a view recursively sql server

Question

I have a view-A that is comprised of other views (View-B View-C) and I would like to list all the tables that used by View-A (which would include the tables used in View-B and View-C). When I use this code...

-- Get base tables for 'View-A' in Database-A 
USE Database-A 
GO
SELECT view_name, Table_Name
FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE View_Name = 'View-A'
ORDER BY view_name, table_name
GO

...I only get the tables that are immediately used by View-A plus the name of the other views (View-B, View-C). Is there a way to get recursively the names of the tables that are also used by View-B and View-C?

Answer 1

I rewrote using sys tables as follows.

DECLARE @ViewName VARCHAR(MAX);
SET @ViewName = 'YOUR_VIEW_NAME';     
WITH deps ( parent, child ) AS ( SELECT v.name ,
            sed.referenced_entity_name
     FROM   sys.sql_expression_dependencies sed
            INNER JOIN sys.views AS v ON sed.referencing_id = v.object_id
     WHERE  v.name = ISNULL(@ViewName, v.name)
     UNION ALL
     SELECT v.name ,
            sed.referenced_entity_name
     FROM   sys.sql_expression_dependencies sed
            INNER JOIN sys.views AS v ON sed.referencing_id = v.object_id
            INNER JOIN deps ON deps.child = v.name ) SELECT   parent ,
         child FROM     deps ORDER BY parent ,
         child;

Answer 2

This query gives you two columns, parent and child, showing the cascading relationships (e.g. view a relies on view b, view b relies on table c, etc.):

with deps (parent, child) as (
    select vtu.view_name, table_name
    from information_schema.view_table_usage as vtu
    where view_name = 'YOUR_VIEW_NAME'
    union all
    select vtu.view_name, vtu.table_name
    from information_schema.view_table_usage as vtu
    inner join deps on deps.child = vtu.view_name
)
select parent, child
from deps;

If you're not worried about the relationships between parent and child, you can change the select to be

select distinct child
from deps;