How to find everything that depends on a SQL Server table before you change it

Renaming a column, changing a data type or retiring a table in a SQL Server warehouse looks like a small change. The risk is everything downstream you can't see: views that select the column, procedures that load from it, SSIS packages that read it and Power BI datasets that report on it. This guide shows how to find those dependencies with what SQL Server already gives you — and where those tools stop.

1. Objects that reference a table: sys.dm_sql_referencing_entities

For a quick list of views, procedures, functions and triggers in the same database that reference a table, use the sys.dm_sql_referencing_entities function:

SELECT referencing_schema_name,
       referencing_entity_name,
       referencing_class_desc
FROM sys.dm_sql_referencing_entities(N'dw.DimCustomer', N'OBJECT');

This only returns direct references. A report view built on top of another view that uses dw.DimCustomer will not appear.

2. The full downstream chain: a recursive query

sys.sql_expression_dependencies stores one row per reference. Walking it recursively gives the whole chain of dependent objects, and the depth at which each one sits:

WITH downstream AS (
    SELECT d.referencing_id, 1 AS depth
    FROM sys.sql_expression_dependencies AS d
    WHERE d.referenced_id = OBJECT_ID(N'dw.DimCustomer')

    UNION ALL

    SELECT d.referencing_id, ds.depth + 1
    FROM sys.sql_expression_dependencies AS d
    JOIN downstream AS ds ON d.referenced_id = ds.referencing_id
    WHERE ds.depth < 10          -- guard against cycles
)
SELECT DISTINCT
       OBJECT_SCHEMA_NAME(ds.referencing_id) AS schema_name,
       OBJECT_NAME(ds.referencing_id)        AS object_name,
       o.type_desc,
       ds.depth
FROM downstream AS ds
JOIN sys.objects AS o ON o.object_id = ds.referencing_id
ORDER BY ds.depth, schema_name, object_name;

3. Column-level dependencies

When only one column is changing, check which columns a dependent object actually uses with sys.dm_sql_referenced_entities. The referenced_minor_name column holds the column name:

SELECT referenced_schema_name,
       referenced_entity_name,
       referenced_minor_name AS column_name
FROM sys.dm_sql_referenced_entities(N'rpt.vw_CustomerRevenue', N'OBJECT')
WHERE referenced_entity_name = N'DimCustomer';

4. What the catalog views miss

Dependency views are a starting point, not a complete answer. They do not reliably capture:

• Dynamic SQL. Anything built as a string and run with EXEC or sp_executesql is invisible to dependency tracking.
• Other databases and servers. Cross-database references are recorded by name only, and linked-server queries are easy to miss.
• SSIS packages. Queries inside data-flow sources, lookups and Execute SQL tasks are stored in the package, not in the database.
• Power BI and other reporting tools. Dataset queries live in the reporting platform.

A text search of module definitions catches some of the dynamic cases, at the cost of false positives:

SELECT OBJECT_SCHEMA_NAME(m.object_id) AS schema_name,
       OBJECT_NAME(m.object_id)        AS object_name
FROM sys.sql_modules AS m
WHERE m.definition LIKE N'%DimCustomer%';

5. Finding table references in SSIS packages

SSIS packages (.dtsx) are XML, so the most dependable approach is to search the package files in source control for the table name. For packages still stored in msdb (package deployment model), the XML can be queried directly:

SELECT p.name AS package_name
FROM msdb.dbo.sysssispackages AS p
WHERE CAST(CAST(p.packagedata AS varbinary(max)) AS varchar(max))
      LIKE '%DimCustomer%';

Projects deployed to the SSIS catalog (SSISDB) are stored in a compressed form, so search the project source instead.

6. Power BI datasets

To see which Power BI datasets query a table, you need the dataset definitions: the Power Query (M) and native SQL behind each table. With the right tenant permissions, Power BI's admin metadata-scanning APIs can return dataset expressions for a whole tenant; otherwise, dataset definitions have to be inspected one by one.

Putting it together

A reliable impact check before a change combines all of these: catalog views for database objects, a text search for dynamic SQL, package search for SSIS and dataset definitions for Power BI — then a list of owners for the affected reports. Doing that by hand for every change is slow, which is why it tends to be skipped.

Dataventra Impact brings these sources into one lineage graph, from source system to report, so the question “what breaks if we change this?” has an answer before the change is made.