ALTER and DROP in SQL Server: find table dependencies before you run it

Find table dependencies before you DROP or ALTER a column in SQL Server, using sys.dm_sql_referencing_entities, sys.foreign_keys and dependency views.

Author: Łukasz Żochowski

  • #sql
  • #sqlserver
  • #ssms
  • #database

A DROP TABLE or an ALTER TABLE ... ALTER COLUMN is a one-line statement with a blast radius you cannot see from the statement itself. The table you are about to change might be read by a dozen views, three reporting procedures, a trigger, and a foreign key from a child table you forgot existed. SQL Server will usually let you run the change anyway — and the breakage shows up later, in production, as a view that no longer compiles or an application query that suddenly errors.

The safe habit is simple: read the dependencies before you run the statement. SQL Server already tracks most of them for you in catalog views and dynamic management functions. This guide shows the exact queries to run first, what each one catches, and — just as important — what none of them catch.

Disclosure: this blog is published by LumaSoft (LUMA sp. z o.o.), and I work on the tool mentioned near the end.

What actually breaks when you ALTER or DROP?

Two different statements, two different kinds of damage.

A DROP removes an object other things may point at. If a view, procedure, or function references a dropped table by name, it does not fail at drop time — it fails the next time someone runs it. If a child table has a foreign key pointing at the table you drop, the engine will refuse the drop outright until the constraint is gone.

An ALTER COLUMN is subtler. Changing a column’s type, length, nullability, or collation can break anything bound to that column: schema-bound views and functions (created WITH SCHEMABINDING), computed columns, CHECK and DEFAULT constraints, indexes that include the column, and any procedure or function that assumes the old shape. Some of these block the ALTER; others let it through and misbehave afterwards.

So there are three questions to answer before you touch anything: what objects reference this one by name, what foreign keys point at it, and — for ALTER COLUMN — who actually uses the specific column I’m changing. SQL Server answers all three.

How do you find what references a table in SQL Server?

Start with the object-level question: which views, procedures, functions, and triggers mention this table by name? The dynamic management function sys.dm_sql_referencing_entities returns exactly that — one row per entity in the current database that references the object you name (Microsoft Learn: sys.dm_sql_referencing_entities):

-- What depends on this table? (views, procedures, functions, triggers)
SELECT
    referencing_schema_name,
    referencing_entity_name,
    referencing_class_desc,
    is_caller_dependent
FROM sys.dm_sql_referencing_entities('Sales.Customer', 'OBJECT');

The second argument is the referenced class — OBJECT, TYPE, XML_SCHEMA_COLLECTION, or PARTITION_FUNCTION; use OBJECT for tables and views. Every row is something that would break, or need revisiting, if you dropped Sales.Customer. An empty result set means nothing in this database references it by name — note the emphasis, because the docs are explicit that the function “does not return entities in other databases.”

How do you find foreign keys pointing at a table?

Foreign keys are a separate kind of dependency: they are not SQL expressions, so the function above does not report them. They live in their own catalog view, sys.foreign_keys, which holds one row per foreign-key constraint (Microsoft Learn: sys.foreign_keys). To find the incoming keys — the child tables whose constraints reference the table you want to drop — filter on referenced_object_id:

-- Which foreign keys point AT this table? (child tables that block a DROP)
SELECT
    fk.name                                  AS foreign_key_name,
    OBJECT_SCHEMA_NAME(fk.parent_object_id)  AS child_schema,
    OBJECT_NAME(fk.parent_object_id)         AS child_table,
    fk.delete_referential_action_desc,
    fk.is_disabled
FROM sys.foreign_keys AS fk
WHERE fk.referenced_object_id = OBJECT_ID('Sales.Customer');

Here parent_object_id is the table that owns the constraint (the child), and referenced_object_id is the table being referenced (your target). The delete_referential_action_desc column tells you whether a delete cascades, sets null, or does nothing — which matters a great deal if you were planning to clear rows before the drop. Any row here is a constraint you must drop or re-point first.

How do you check column-level dependencies before ALTER COLUMN?

ALTER COLUMN needs a finer answer than “what references the table.” You want to know who touches that column. Two views help.

First, sys.sql_expression_dependencies gives you every by-name dependency on an entity, and crucially flags schema-bound references — the ones that will hard-block your ALTER (Microsoft Learn: sys.sql_expression_dependencies). Querying it in reverse (what references my object) looks like this:

-- Everything that references Sales.Customer by name, schema-bound flagged
SELECT
    OBJECT_SCHEMA_NAME(sed.referencing_id) AS referencing_schema,
    OBJECT_NAME(sed.referencing_id)        AS referencing_name,
    o.type_desc,
    sed.is_schema_bound_reference
FROM sys.sql_expression_dependencies AS sed
JOIN sys.objects AS o
    ON sed.referencing_id = o.object_id
WHERE sed.referenced_id = OBJECT_ID(N'Sales.Customer');

Rows with is_schema_bound_reference = 1 are the urgent ones: a schema-bound view or function pins the column in place and the engine will reject the ALTER until you change it.

Second, to confirm whether a specific module reads or writes the column, point sys.dm_sql_referenced_entities at that module. It returns column-level detail, including whether each column is selected or updated (Microsoft Learn: sys.dm_sql_referenced_entities):

-- Does this procedure read or write the column I'm about to ALTER?
SELECT
    referenced_entity_name AS table_name,
    referenced_minor_name  AS column_name,
    is_selected,
    is_updated,
    is_select_all
FROM sys.dm_sql_referenced_entities('Sales.uspUpdateCustomer', 'OBJECT');

A column flagged is_updated = 1 whose type you are about to shrink is exactly the kind of thing you want to catch before the change, not after the next overnight job fails.

What won’t these queries catch?

This is the part worth internalising, because a dependency check that gives you false confidence is worse than none.

  • Dynamic SQL and application code. These views track persisted, by-name references inside SQL modules. A query assembled as a string with EXEC(@sql), or built in your C# or Python layer, is invisible to them. Those dependencies only live in code you have to grep yourself.
  • Cross-database references. sys.dm_sql_referencing_entities reports only the current database. A view in another database that selects from your table will not show up. sys.sql_expression_dependencies records cross-database names but cannot resolve their IDs.
  • The old sp_depends. If you learned this on an older server, drop the habit: Microsoft marks sp_depends as deprecated — “This feature will be removed in a future version of SQL Server… Use sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities instead” (Microsoft Learn: sp_depends).

Treat the catalog views as a strong first pass, then confirm with a grep of your application repositories for anything built dynamically.

What’s the equivalent in PostgreSQL and MySQL?

Briefly, because the principle travels. PostgreSQL records dependencies in the pg_depend catalog, and leans on them at drop time: DROP defaults to RESTRICT, which “refuses to drop the table if any objects depend on it,” while CASCADE drops the dependents too (PostgreSQL: pg_depend, DROP TABLE). The engine protecting you by default is a real advantage — but you still want to know what CASCADE would take with it before you type it. MySQL exposes foreign-key relationships through information_schema.KEY_COLUMN_USAGE; filter on REFERENCED_TABLE_NAME to find the constraints pointing at a table (MySQL: KEY_COLUMN_USAGE).

The takeaway

Every query above is a SELECT against a system view. They are read-only, instant, and free — there is no reason not to run them before a structural change. Reading the dependencies first turns ALTER and DROP from a gamble into a decision you can defend.

This is also a small example of a bigger principle. Knowing what depends on a table is a fact you look up from the live database, not something you reason about from memory or a stale diagram — and it is precisely the kind of thing an AI assistant should read from your real schema instead of guessing. Because these dependency queries are pure reads, they also sit comfortably inside a read-only-by-default workflow: look before you leap, and keep the looking separate from the leaping.

If you work in SQL Server Management Studio, SQL Sage for SSMS is an add-in that puts an AI copilot in the query editor — it writes, explains, and optimizes SQL grounded in your real schema, stays read-only by default (a SELECT runs; anything that changes data or structure waits for your explicit confirmation), and sends only your schema and SQL text to the model, never your data rows unless you opt in per session. It is keyless — you sign in with your own Claude or ChatGPT account, no API key to manage. There is a 30-day trial with no card, then a free tier. You can see it and the DBeaver sibling on the SQL Sage products page. SQL Sage is made by LumaSoft (LUMA sp. z o.o.) and is not affiliated with Microsoft.

← Back to the blog