How can i check sql server 'views' dependencies

Question

Is there a way that i can find out what base tables are being used by views using a custom query or stored procedure?

Answer 1

You could use the sys.dm_sql_referenced_entities function to find objects referenced by a specified view:

SELECT DISTINCT 
referenced_schema_name , 
referenced_entity_name 
FROM sys.dm_sql_referenced_entities ('Sales.vSalesPersonSalesByFiscalYears', 'OBJECT');

Also, there is the sys.sql_expression_dependencies system view where you can specify a table name and a type of the referencing object:

SELECT 
referencing_object_name = o.name, 
referencing_object_type_desc = o.type_desc 
FROM sys.sql_expression_dependencies se 
INNER JOIN sys.objects o 
ON se.referencing_id = o.[object_id] 
WHERE referenced_entity_name = 'Person' AND o.type_desc = 'View'

To avoid "manual" work you could also use ApexSQL Clean, a SQL Server tool that can find all internal and external dependencies. In the results pane select an object and see all objects that depend on the selected object, and objects on which the selected object depends on:

You can also filter objects and visualize dependencies:

Disclaimer: I work for ApexSQL as a Support Engineer

Hope this helps