Finding Every Object That Uses a Column

A column rename looks small until a report fails tomorrow. Finding every object that uses a column takes dependency metadata, text search, and a check outside the database.

Start With the Exact Column Before Finding the Object That Uses It

Record the database, schema, table, and column name. A column named Status exists in many tables. Search by name alone and the result becomes noise. Confirm the object_id and column_id in sys.columns before tracing references.

I ask what change you plan: rename, type change, drop, or permission change. Each has different risks. A type change can affect implicit conversions without a literal column name in a module. A rename can break a view that still parses until it runs.

Which applications call the table directly? Ask the owner now. The database catalog cannot see every query built in an application service. That missing part of the map is usually where the surprise waits.

SELECT
    SCHEMA_NAME(o.schema_id) AS SchemaName,
    o.name AS TableName,
    c.name AS ColumnName,
    o.object_id,
    c.column_id
FROM sys.objects AS o
JOIN sys.columns AS c
  ON c.object_id = o.object_id
WHERE o.type = 'U'
ORDER BY SchemaName, TableName, c.column_id;

Read Persisted Dependencies

sys.sql_expression_dependencies records by name references in persisted SQL expressions. It can identify a view or procedure that references another object. For schema bound definitions, column level information is more complete. Non schema bound references can resolve only to an object level row. Treat a missing column row with care.

The view carries referencing_id and referenced_id for resolved local objects. It also carries names for cross database and server references where a local id is unavailable. Join to sys.objects to see the referencing module.

I use this as the first pass because it is structured. It is not a full guarantee. SQL assembled into a string at runtime is not represented as a normal persisted dependency.

Look for Column-Level Rows

When referenced_minor_id identifies a column, join it to sys.columns under the referenced object. The query below shows these resolved column references across the current database. It can return no row for a real use because dependency tracking has documented limits.

Read the referencing object and referenced column together. A module can refer to a table through SELECT star, and metadata can reflect the table or columns differently from a literal named reference. Review the module definition before changing the column.

Do not turn a zero row count into a release approval. It is one view’s answer under your account and database context. Check metadata permissions if expected modules are absent.

SELECT
    SCHEMA_NAME(o.schema_id) AS ReferencingSchema,
    o.name AS ReferencingObject,
    OBJECT_SCHEMA_NAME(d.referenced_id) AS ReferencedSchema,
    OBJECT_NAME(d.referenced_id) AS ReferencedObject,
    c.name AS ReferencedColumn
FROM sys.sql_expression_dependencies AS d
JOIN sys.objects AS o
  ON o.object_id = d.referencing_id
JOIN sys.columns AS c
  ON c.object_id = d.referenced_id
 AND c.column_id = d.referenced_minor_id
WHERE d.referenced_minor_id > 0
ORDER BY ReferencingSchema, ReferencingObject;

Search Module Text for Any Object That Uses a Column

sys.sql_modules stores definitions for visible T-SQL modules. Search the definition text for the column name. This catches some references that the dependency view does not present at column level. It also produces false positives in comments, aliases, and unrelated tables. Review each hit.

The search below uses CustomerId as a placeholder. Replace it with the real column name before trusting results. The query itself runs on any database, but the word you choose determines whether it answers your question.

I search for both the column and the table name when the column is common. Then I read the surrounding SQL. A text hit is a lead, not proof that the object uses the target column.

SELECT
    SCHEMA_NAME(o.schema_id) AS SchemaName,
    o.name AS ModuleName,
    o.type_desc
FROM sys.sql_modules AS m
JOIN sys.objects AS o
  ON o.object_id = m.object_id
WHERE m.definition LIKE N'%CustomerId%'
ORDER BY SchemaName, ModuleName;

Watch Dynamic SQL

Dynamic SQL can build a column name from variables or configuration. The dependency view cannot resolve every runtime string. A text search can miss the final name if it is assembled from pieces. Search job steps, configuration tables, and application source through approved tools.

Look for EXEC, sp_executesql, and generated SELECT lists in modules that touch the table. Review the actual paths used in production. A module with no literal target column can still produce it at runtime. This is why a database only search cannot prove “every” use.

When I cannot inspect the application source, I say so in the change review. The unknown belongs in the risk record and the test plan. It should not be hidden behind a confident query result.

Check Views and Reporting

A view can expose a column to reports even when the report never names the base table. Follow dependencies through views, then search report definitions and downstream datasets. A rename can affect a chain several steps away.

Use sys.dm_sql_referenced_entities for a focused module when you need more detail on non schema bound references. Check its results and errors for the specific object. It is a complement to the catalog view, not a magic fleet wide parser.

I test a representative report after any column change. A database object can compile while a client expects the old column name in the result set. The application contract includes names and types, not only query success.

Run a Safe Change Rehearsal

Restore current data to a test instance. Apply the proposed change there. Refresh modules as required by the plan and execute the known application paths. Check jobs, reports, imports, and API calls. Keep the same driver and connection settings where practical.

A static search can identify candidates. Only a workload test shows whether a real path breaks. Use monitoring after production deployment to catch paths that the rehearsal did not cover. Keep a rollback that respects data changes during the window.

If the column is widely used, consider a staged migration: add a new column, update clients, then retire the old one. That can be safer than a sudden rename. The extra step is cheaper than a surprise outage.

Document the Coverage for Every Object That Uses a Column

List what the dependency query found, what text search found, what application owners checked, and what remains unknown. Keep the exact database and permission context for each search. A result from one development database is not proof about production.

If the column is dropped, preserve a backup and a schema script. If it is renamed, document aliases or compatibility views used during transition. Put an owner and expiry date on temporary compatibility code.

The phrase “every object” is a goal, not a promise from one DMV. Combine the tools and state the limits. That gives the reviewer a decision grounded in evidence.

Related reading on this blog: Find Referenced or Referencing Object in SQL Server using sys.sql_expression_dependencies and Tracking Database Dependencies.

A column dependency is not one catalog row, it is every path that expects the old shape.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.