patternsqlMinor
Find all tables that are related to a view recursively sql server
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...
...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?
-- 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.