What Breaks If I Drop This Column? sys.sql_expression_dependencies

Removing a column can break code that never appears in the proposed change script. The sql_expression_dependencies catalog is a useful starting point for finding those relationships before a drop or rename reaches production.

Define the Column Before Querying sql_expression_dependencies

Identify the database, schema, table, and column before searching. A column name such as Status can occur in many unrelated objects. A text search for that word produces candidates, while a resolved object-and-column identity provides stronger evidence about the intended target. Keep those evidence levels distinct in the review.

I collect the current definition and accepted replacement plan before proposing removal. A rename changes a name that callers use; a drop also removes stored data. Each needs its own compatibility and recovery decision. Do not let a clean dependency report silently authorize either action.

The examples use fresh scratch objects to demonstrate schema-bound and ordinary module references. Run the investigative queries read-only against the real target after substituting its verified identity. Ensure the review identity can see relevant definitions; restricted metadata visibility can make an incomplete result look reassuringly empty.

Create Two Different Module References

The schema-bound view selects the candidate column explicitly. The procedure selects other columns from the same table. These contrasting references help show why a table-level dependency alone does not identify every column a module uses. Each module definition is isolated in its own batch.

CREATE TABLE dbo.CustomerAccount
(
    CustomerID int NOT NULL PRIMARY KEY,
    CustomerName nvarchar(60) NOT NULL,
    LegacyCode nvarchar(20) NULL
);
GO
CREATE OR ALTER VIEW dbo.CustomerCodeView
WITH SCHEMABINDING
AS
SELECT CustomerID,LegacyCode
FROM dbo.CustomerAccount;
GO
CREATE OR ALTER PROCEDURE dbo.GetCustomerNames
AS
BEGIN
    SELECT CustomerID,CustomerName
    FROM dbo.CustomerAccount;
END;
GO

Schema binding gives the first reference a stronger enforced relationship. SQL Server can reject a conflicting column drop while that view depends on it. Ordinary module references and external callers still need review even when they do not block the DDL in the same way. A successful ALTER statement does not prove that every caller remains usable afterward.

Read Column Rows From sql_expression_dependencies

The catalog provides referenced_minor_id rather than a referenced column-name field. Resolve the name from the referenced object and column ID. Include object-level rows whose minor ID is zero as candidates needing further inspection, rather than discarding them from a column review.

DECLARE @TableID int=OBJECT_ID(N'dbo.CustomerAccount');
DECLARE @ColumnID int=
    (SELECT column_id FROM sys.columns
     WHERE object_id=@TableID AND name=N'LegacyCode');
IF @TableID IS NULL OR @ColumnID IS NULL
    THROW 50000,'Verify the target table and column.',1;
SELECT OBJECT_SCHEMA_NAME(d.referencing_id) AS ReferencingSchema,
       OBJECT_NAME(d.referencing_id) AS ReferencingObject,
       o.type_desc AS ReferencingType,d.is_schema_bound_reference,
       d.referenced_minor_id,
       COL_NAME(d.referenced_id,d.referenced_minor_id) AS ReferencedColumn
FROM sys.sql_expression_dependencies AS d
LEFT JOIN sys.objects AS o ON o.object_id=d.referencing_id
WHERE d.referenced_id=@TableID
  AND d.referenced_minor_id IN (0,@ColumnID);

The sql_expression_dependencies result identifies persisted relationships within its documented scope. For ordinary non-schema-bound modules, column details can require the referenced-entity function. Treat an object-level candidate as unresolved column usage until the module's actual references are examined. Zero is not a secret column named nothing.

Find Referencing Modules and Expand Their Column Use

The referencing-entity function lists local entities that refer to the specified object. It starts from the table, not a column-level target. For each relevant module, inspect its referenced entities to obtain column details where binding succeeds. Keep errors and unresolved references visible instead of treating them as absence.

SELECT referencing_schema_name,referencing_entity_name,
       referencing_id,referencing_class_desc,is_caller_dependent
FROM sys.dm_sql_referencing_entities(N'dbo.CustomerAccount',N'OBJECT');
SELECT referenced_schema_name,referenced_entity_name,
       referenced_minor_name,referenced_minor_id,is_select_all
FROM sys.dm_sql_referenced_entities(N'dbo.GetCustomerNames',N'OBJECT')
WHERE referenced_entity_name=N'CustomerAccount';

The second function does expose referenced_minor_name; the catalog used in the previous query does not. Use the documented fields for the exact object being queried. For non-schema-bound modules, unbound referenced entities can prevent complete column reporting. Record that incomplete scope and inspect the module text directly.

Add a Literal Text Search as Supporting Evidence

Search visible module definitions for the candidate name. This can reveal dynamic SQL and references the resolved catalog did not describe. It can also match comments, string literals, unrelated columns, and similarly named objects. The output is a review queue rather than a reliable semantic dependency list.

SELECT s.name AS SchemaName,o.name AS ModuleName,o.type_desc,m.definition
FROM sys.sql_modules AS m
JOIN sys.objects AS o ON o.object_id=m.object_id
JOIN sys.schemas AS s ON s.schema_id=o.schema_id
WHERE CHARINDEX(N'LegacyCode',m.definition)>0;
SELECT s.name AS SchemaName,o.name AS ModuleName
FROM sys.sql_modules AS m
JOIN sys.objects AS o ON o.object_id=m.object_id
JOIN sys.schemas AS s ON s.schema_id=o.schema_id
WHERE m.definition IS NULL;

The NULL-definition list identifies modules needing a visibility or encryption investigation. An unavailable definition cannot be accepted as a negative text-search result. I examine both positive candidates and unavailable evidence before reporting the review's coverage. A search that cannot read its input has a limited opinion about what the input contains.

Inspect Constraints, Indexes, and Table Expressions

The application modules are only part of the change impact. Index keys and included columns, defaults, checks, computed columns, foreign keys, and specialized table features can depend on the target. Inspect their definitions and state before drafting a drop or rename sequence.

DECLARE @TableID int=OBJECT_ID(N'dbo.CustomerAccount');
SELECT i.name AS IndexName,c.name AS ColumnName,
       ic.key_ordinal,ic.is_included_column
FROM sys.index_columns AS ic
JOIN sys.indexes AS i ON i.object_id=ic.object_id AND i.index_id=ic.index_id
JOIN sys.columns AS c ON c.object_id=ic.object_id AND c.column_id=ic.column_id
WHERE ic.object_id=@TableID AND c.name=N'LegacyCode';
SELECT name,definition
FROM sys.default_constraints
WHERE parent_object_id=@TableID
  AND parent_column_id=COLUMNPROPERTY(@TableID,N'LegacyCode','ColumnId');
SELECT name,definition FROM sys.computed_columns WHERE object_id=@TableID;

A blocked DDL operation can expose one enforced dependency, but it is not a complete discovery process. Review all relevant categories before the change window. Preserve the target's data when removal has a rollback requirement; recreating the column definition alone does not recreate its old values.

What sql_expression_dependencies Cannot See

Application-generated SQL, dynamically assembled identifiers, Agent step text, and external reports need separate review. Other databases can contain references to this database, while a local query does not inventory every remote catalog. Cross-server callers add another boundary. Define which owners and systems must confirm the change.

Search accepted application and job sources through the approved project process and exercise representative call paths on a restored copy. Include SELECT star consumers whose expected result shape can change after a column alteration. A consumer can break because a result contract changed even when it never names the column explicitly.

Which caller uses this table only during a rare operational task? Include that question with the owner review. Routine traffic alone cannot validate month-end, recovery, or support paths. Keep the untested paths documented rather than treating silence during a short observation window as comprehensive evidence.

Rehearse the Compatibility Decision

Combine resolved dependencies, text candidates, unavailable definitions, and external-owner confirmations into one reviewed impact list. Update accepted callers first where a phased compatibility strategy is appropriate. Rehearse the final column change and the actual application behavior on a copy with the expected schema and permissions.

Capture the review time, database name, visible object definitions, and the intended deployment revision. Recheck the impact list immediately before execution if another deployment changed those inputs. A dependency review belongs to a particular schema state; a clean report from last week cannot certify an altered database today.

Use sql_expression_dependencies as one strong input in that process, then verify the remaining boundaries. Retain the exact reviewed definition and recovery plan with the proposed change. The useful answer to what breaks is an evidence-backed impact list, not an empty query result presented as a universal guarantee.

Related reading on this blog: Finding Every Object That Uses a Column and Renaming a Column Safely Everywhere It Is Used.

An empty dependency result is not permission to drop a column, it is one observation within a wider compatibility review.

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