HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

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

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
tablesallaresqlrelatedviewrecursivelythatfindserver

Problem

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?

Solution

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;

Code Snippets

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;

Context

StackExchange Database Administrators Q#102281, answer score: 2

Revisions (0)

No revisions yet.