A table column is about to change, and the obvious procedures are only part of the story. Finding what depends on a table takes catalog evidence and a search beyond the current database.
Start With the Table’s Exact Name
Record schema, table, and proposed change before searching. A common table name can exist in several schemas. A column rename affects a narrower set of consumers than dropping the whole table, but both need an inventory.
I ask what the change would break: reads, writes, permissions, reports, or an ETL mapping. That answer determines where to search. A database dependency function is useful, but it cannot see every application query.
Test the current table definition and key columns. A dependency report without a precise target name can send you through unrelated objects.
SELECT OBJECT_ID(N'dbo.Customer') AS ObjectId;
SELECT name, column_id
FROM sys.columns
WHERE object_id = OBJECT_ID(N'dbo.Customer')
ORDER BY column_id;Use the Referencing Entities Function to See What Depends on a Table
sys.dm_sql_referencing_entities returns entities in the current database that reference a specified object. Supply a schema-qualified name and OBJECT class. Its documented output includes referencing schema, entity name, ID, class, and caller-dependent status.
I use it as a first-pass list of local T-SQL modules. Review each result’s actual definition and execution path. A recorded reference does not tell you whether the branch runs in production or which column matters.
A zero-row result is not proof of no consumers. The function is scoped and subject to metadata visibility. Dynamic SQL and external code need their own searches.
SELECT referencing_schema_name, referencing_entity_name,
referencing_id, referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities(N'dbo.Customer', N'OBJECT');Check Text and Dependencies Together
Search sys.sql_modules for the table name and review matches. A text hit can be a comment, string literal, or dynamic SQL. A dependency function can miss constructed SQL. The two methods complement each other and both require human review.
I keep a column for match type in the inventory. Active static reference, dynamic reference, comment, or unrelated text are different findings. That stops a large raw result list from looking like a complete change plan.
Search old and new names when the change is a rename. A procedure can use an alias that hides the table name in its SELECT but still depends on a view over it. Follow the chain.
SELECT OBJECT_SCHEMA_NAME(object_id) AS schema_name,
OBJECT_NAME(object_id) AS module_name
FROM sys.sql_modules
WHERE definition LIKE N'%Customer%';
Look Across Database Boundaries for What Depends on a Table
A module in another database can reference this table with a three-part name. The current database’s referencing-entities function does not list every cross-database caller. Search modules across accessible databases and record which databases were not searched.
I also inspect sys.sql_expression_dependencies for explicit database names where it helps. Name-based references can appear even when an object ID cannot be resolved across databases. Do not confuse NULL referenced_id with no relationship.
Keep server and database names visible in the inventory. A staging database can contain the job that writes production data. A table can look unused locally while a reporting database reads it every minute.
SELECT OBJECT_SCHEMA_NAME(referencing_id) AS schema_name,
OBJECT_NAME(referencing_id) AS module_name,
referenced_database_name, referenced_schema_name,
referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referenced_entity_name = N'Customer';Search Outside SQL Modules
SQL Agent steps, SSIS packages, report datasets, Power BI models, and application code can name the table. Some use synonyms or views, which hide the base name. Search approved project sources and inventory the data paths that reach the object.
I ask owners of scheduled and monthly processes to review the list. A change can pass daily tests and fail at month end. The database catalog cannot know what a client sends as ad hoc SQL.
Encrypted modules and insufficient metadata permission are blind spots. Record them as unresolved rather than calling them clean. An impact assessment should be honest about its search coverage.
SELECT j.name AS job_name, s.step_name, s.command
FROM msdb.dbo.sysjobsteps AS s
JOIN msdb.dbo.sysjobs AS j ON j.job_id = s.job_id
WHERE s.command LIKE N'%Customer%';Turn What Depends on a Table Into a Test Plan
For each active consumer, record owner, expected behavior, change needed, and test. A view can need metadata refresh, a procedure can need a changed column name, and an application can need a coordinated release. A list of object names alone is not enough.
I test read and write paths under actual identities. A module can compile while a dynamic branch fails only with certain parameters. Include those branches and less frequent jobs. Check the result, not just whether the call returned.
Keep a rollback or compatibility path when consumers cannot all change at once. A view alias can support reads for a time, but old writes need a separate plan. Dependency discovery leads to coordination.
SELECT name, modify_date
FROM sys.objects
WHERE type IN ('P', 'V', 'FN', 'IF', 'TF')
ORDER BY modify_date DESC;Recheck After the Change
Run the same dependency and text searches after deployment. Review remaining old-name hits. Some can be comments or intentional compatibility code. Record why they remain and when temporary aliases will be removed.
I monitor errors from jobs and applications after release. A rarely used path can appear later. Keep the original inventory available so the owner of that path can be found quickly.
Finding what depends on a table is not one DMV call. It is a bounded search across local metadata, other databases, and outside consumers, followed by tests. The useful result is a change plan with known blind spots.
Dependency metadata is a useful first map, but it does not understand dynamic SQL assembled at runtime. A procedure that builds a table name from a variable can reference a table without appearing in the map. Cross-database references can also have incomplete identifiers, depending on how the module was written. Which code paths construct names rather than naming objects directly? I search those paths in source files and review the calling application as well.
Before changing a table, inventory synonyms, jobs, reports, and external consumers. Ask the owners to validate the proposed change against their queries. I also look for wildcard SELECT statements because a new or removed column can change their result shape without a broken dependency warning. A dependency report starts the conversation. It cannot sign off the deployment.
After a schema change in a test environment, execute representative dependent modules and compare outputs. Refresh module metadata where the documented workflow requires it. Record any dependency that is discovered outside SQL Server so the next review has a better map. A complete answer comes from metadata, source inspection, and actual use.
Related reading on this blog: Tracking Database Dependencies and Find Referenced or Referencing Object in SQL Server using sys.sql_expression_dependencies.
A dependency list is not a guarantee of safety, it is the beginning of a tested change plan.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
