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.