Map Views Dependency in SQL Server Database
While working on an ETL framework, we had the need to know, which views are dependent on which table, regardless of the depth.
Each object in the data mart had a view that joins and maps the data from the EDW layer in the data mart table. Our ETL framework would create a record every time new data was added, deleted or updated in the EDW layer, and we wanted to use that information to know which tables in the data mart needs to be updated due to a change in one or more depending objects in EDW.
The query to list the dependency hierarchy is utilizing the recursive CTE feature of SQL Server.
with dependencies as /*this recursive CTE will list all the tables in the database, and then list all the views that depended on the table, no matter how deep the dependency go (as long as it via views). */ ( select dep.referenced_entity_name, OBJECT_NAME(dep.referencing_id) AS referencing_entity_name, OBJECT_SCHEMA_NAME(dep.referenced_id) + '.' + OBJECT_NAME(dep.referenced_id) as source, object_schema_name(referencing_id) + '.' + object_name(referencing_id) as dependent, referencing_id as dependent_object_id, 1 as depth, cast(OBJECT_SCHEMA_NAME(dep.referenced_id) + '.' + OBJECT_NAME(dep.referenced_id) + ' -> ' + object_schema_name(referencing_id) + '.' + object_name(referencing_id) as varchar(400)) as path, so.type_desc AS source_type, do.type_desc AS dependent_type from sys.sql_expression_dependencies as dep inner join sys.objects as so on so.object_id = dep.referenced_id inner join sys.objects as do on do.object_id = dep.referencing_id where dep.referenced_id <> dep.referencing_id and so.type = 'U' AND do.type = 'V' union all select dep2.referenced_entity_name, OBJECT_NAME(dep.referencing_id) AS referencing_entity_name, dep2.source, object_schema_name(referencing_id) + '.' + object_name(referencing_id) as dependent, dep.referencing_id as dependent_object_id, dep2.depth + 1 as depth, cast(dep2.path + ' -> ' + object_schema_name(referencing_id) + '.' + object_name(referencing_id) as varchar(400)) as path, dep2.source_type, dep2.dependent_type from sys.sql_expression_dependencies as dep inner join dependencies as dep2 ON dep.referenced_entity_name = dep2.referencing_entity_name and dep2.source <> object_schema_name(referencing_id) + '.' + object_name(referencing_id) ) select d.source , d.dependent , d.dependent_object_id , d.depth , d.path , d.source_type, d.dependent_type from dependencies as d
The result for this query against the AdventureWorksDW will show this:
The source column lists the name of the database object (table or view) that the view depends on. The dependent column lists the name of the dependent view. The dependent_object_id is the object id of the view. The depth tells us what is the distance between the dependent view and the source object. 1 means that the dependent view is accesing the source object directly, while 2 means that the view is using a nother view, that is then dependent on the source item. Since the query is recursive, it can show depths of 3, 4 and more, however it is most likely not to be the case for most database implementations. The path column list the path from source to dependent view.
You can modify this SQL to also show more then views, or show dependency between views, to do that you will need to adjust the object types constraint in the first where clause.