Removing a column from a table looks simple when the application no longer uses it: ALTER TABLE ... DROP COLUMN. But if the column is tied to a default constraint, a check constraint, a foreign key, an index or a manually created statistic, the command fails. More importantly, dropping a column without being sure nobody really uses it means a report or a nightly job suddenly breaking.
This article shares a two-stage method for retiring columns safely, and a query that generates the drop script automatically, together with the dependent objects.
Have unused structures piled up in your database?
Aryasoft identifies unused tables, columns and indexes along with their dependencies and prepares a safe cleanup plan.
A Two-Stage Method: Mark First, Drop Later
Instead of dropping a column you think is unused right away, first mark it as “retired” by renaming it. For example, add a dep_ prefix to its name:
EXEC sys.sp_rename N'dbo.Customer.FaxNo', N'dep_FaxNo', N'COLUMN';If a query, procedure or report still uses the column, it fails with “Invalid column name” after the rename and reveals itself. That failure is easy to undo: rename the column back, and no data is lost. If no error appears during a waiting period (for example a full business cycle, including a month-end close), the column really is unused.
Warning
sp_rename doesn’t update the stored procedures, views and functions that reference the column; because they can’t compile with the new name, they fail when they run. That’s the point of the method, but check dependencies before renaming and share the change with the application teams.
Some dependencies block the rename itself: if a CHECK constraint, a computed column, the WHERE clause of a filtered index, or a schema-bound view or function uses the column, sp_rename fails with Msg 15336 or Msg 5074. In our tests, default constraints, indexes, statistics and foreign keys didn’t block it. If you hit this error, script out the dependent object, drop it, rename the column, and recreate the object with the new name only if it’s still needed.
To find dependencies, use the column-level query in our guide to finding objects that use a table.
Generating the Drop Script
Once the waiting period is over, the query below generates the needed commands, in the right order, for every column whose name starts with dep_: first the constraints, indexes and statistics tied to the column, then the column itself. The query only generates a script; it doesn’t drop anything.
DECLARE @prefix sysname = N'dep[_]%'; -- the underscore is escaped because it's a LIKE wildcard
WITH target AS (
SELECT c.object_id, c.column_id,
QUOTENAME(OBJECT_SCHEMA_NAME(c.object_id)) + N'.' + QUOTENAME(OBJECT_NAME(c.object_id)) AS table_name,
QUOTENAME(c.name) AS column_name
FROM sys.columns AS c
JOIN sys.tables AS t ON t.object_id = c.object_id
WHERE c.name LIKE @prefix
)
SELECT step, command
FROM (
-- 1. Default constraints
SELECT 1 AS step, N'ALTER TABLE ' + tg.table_name + N' DROP CONSTRAINT ' + QUOTENAME(dc.name) + N';' AS command
FROM target AS tg
JOIN sys.default_constraints AS dc ON dc.parent_object_id = tg.object_id AND dc.parent_column_id = tg.column_id
UNION ALL
-- 2. Column-level check constraints
SELECT 2, N'ALTER TABLE ' + tg.table_name + N' DROP CONSTRAINT ' + QUOTENAME(cc.name) + N';'
FROM target AS tg
JOIN sys.check_constraints AS cc ON cc.parent_object_id = tg.object_id AND cc.parent_column_id = tg.column_id
UNION ALL
-- 3. Foreign keys that include the column
SELECT DISTINCT 3, N'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id)) + N'.'
+ QUOTENAME(OBJECT_NAME(fk.parent_object_id)) + N' DROP CONSTRAINT ' + QUOTENAME(fk.name) + N';'
FROM target AS tg
JOIN sys.foreign_key_columns AS fkc
ON (fkc.parent_object_id = tg.object_id AND fkc.parent_column_id = tg.column_id)
OR (fkc.referenced_object_id = tg.object_id AND fkc.referenced_column_id = tg.column_id)
JOIN sys.foreign_keys AS fk ON fk.object_id = fkc.constraint_object_id
UNION ALL
-- 4. Indexes that include the column (review: the index may include other columns)
SELECT DISTINCT 4, N'DROP INDEX ' + QUOTENAME(i.name) + N' ON ' + tg.table_name + N'; -- review'
FROM target AS tg
JOIN sys.index_columns AS ic ON ic.object_id = tg.object_id AND ic.column_id = tg.column_id
JOIN sys.indexes AS i ON i.object_id = ic.object_id AND i.index_id = ic.index_id
WHERE i.is_primary_key = 0 AND i.is_unique_constraint = 0
UNION ALL
-- 5. Manually created statistics
SELECT DISTINCT 5, N'DROP STATISTICS ' + tg.table_name + N'.' + QUOTENAME(s.name) + N';'
FROM target AS tg
JOIN sys.stats_columns AS sc ON sc.object_id = tg.object_id AND sc.column_id = tg.column_id
JOIN sys.stats AS s ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id
WHERE s.user_created = 1
UNION ALL
-- 6. The column itself
SELECT 6, N'ALTER TABLE ' + tg.table_name + N' DROP COLUMN ' + tg.column_name + N';'
FROM target AS tg
) AS s
ORDER BY step, command;Critical
Don’t run the generated script directly. The indexes in step 4 in particular may include other columns and be used by other queries; dropping an index can slow those queries down. If the column is part of a primary key or unique constraint, the script doesn’t drop it; that case needs separate consideration. Review the script, run it in a test environment, and take a backup right before running it in production.
Why Isn’t the Space Back Right Away?
DROP COLUMN is mostly a metadata operation: the column leaves the definition, but the space its data used stays in the pages. To reclaim it, rebuild the table:
-- Tables with a clustered index
ALTER INDEX ALL ON dbo.Customer REBUILD;
-- Heaps
ALTER TABLE dbo.Customer REBUILD;On large tables this consumes resources and generates log; do it in a maintenance window. To measure table sizes before and after, see our table size guide.
Checklist
- The column’s dependencies were checked and shared with the application teams.
- The column was first renamed with the dep_ prefix, and a business cycle passed.
- The drop script was generated, reviewed and run in a test environment.
- The impact of dropping indexes on other queries was assessed.
- A backup was taken before running in production; space was reclaimed afterwards.
Related Reading
SQL Server Consulting
Simplify your database safely
Aryasoft’s senior DBA team identifies unused tables, columns and indexes along with their dependencies, and simplifies your database with a two-stage, reversible cleanup plan.
