Drop Multiple Columns in One ALTER TABLE Statement

To drop multiple columns, list them after one DROP COLUMN in one ALTER TABLE statement. SQL Server changes the table once, and either every column goes or none does.

One Statement Instead of Many

A performance review found unused indexes and unused columns in a table. The DBA dropped the columns one at a time, which took a long time. A single statement does the same work. The syntax names the columns after one DROP COLUMN, separated by commas. The keyword is not repeated.

The demo creates a database named DropColumnsDemo and a table of 50,000 parts. Two of its columns, LegacyCode and LegacyNote, are 400 characters wide and no longer used. Status has a default, Price has a check constraint, and PartName has an index. The script uses a system view only to produce 50,000 numbers, so it runs on any version.

IF DB_ID(N'DropColumnsDemo') IS NULL CREATE DATABASE DropColumnsDemo;
GO
USE DropColumnsDemo;
GO
DROP TABLE IF EXISTS dbo.Parts;
CREATE TABLE dbo.Parts (
    PartID     int           NOT NULL,
    PartName   nvarchar(60)  NOT NULL,
    LegacyCode char(400)     NOT NULL,
    LegacyNote char(400)     NOT NULL,
    Status     nvarchar(12)  NOT NULL CONSTRAINT DF_Parts_Status DEFAULT N'Active',
    Price      decimal(9,2)  NOT NULL,
    CONSTRAINT PK_Parts PRIMARY KEY (PartID),
    CONSTRAINT CK_Parts_Price CHECK (Price > 0)
);
CREATE INDEX IX_Parts_PartName ON dbo.Parts (PartName);
GO
INSERT INTO dbo.Parts (PartID, PartName, LegacyCode, LegacyNote, Price)
SELECT TOP (50000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), N'Part', 'X', 'Y', 10.00
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;

First note how many pages the table uses. Then drop the two legacy columns in one statement.

SELECT in_row_data_page_count AS DataPages
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID(N'dbo.Parts') AND index_id = 1;
GO
ALTER TABLE dbo.Parts DROP COLUMN LegacyCode, LegacyNote;

The Space Comes Back After a Rebuild

The statement finishes at once, but the page count doesn’t change. Dropping a fixed-length column is a metadata change. The old bytes stay in every row until a rebuild rewrites the pages. The next script reads the page count again, rebuilds all indexes and reads it a third time.

SELECT in_row_data_page_count AS DataPages FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID(N'dbo.Parts') AND index_id = 1;
ALTER INDEX ALL ON dbo.Parts REBUILD;
SELECT in_row_data_page_count AS DataPages FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID(N'dbo.Parts') AND index_id = 1;

The page count is the same after the drop and falls from 5,556 to 272 after the rebuild. Plan the rebuild with the drop, and run both in a quiet period.

All or Nothing, and Dependencies

A column that something else depends on can’t be dropped. A default, a check constraint, an index key or a computed column all count. To drop multiple columns that include such a column, deal with the dependency first, or in the same statement. Try to drop Price and Status together.

ALTER TABLE dbo.Parts DROP COLUMN Price, Status;

Msg 5074, Level 16, State 1, Line 1
The object 'CK_Parts_Price' is dependent on column 'Price'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE DROP COLUMN Price failed because one or more objects access this column.

The statement failed as a whole, and both columns still exist. Status has a default constraint of its own, but the error names only the first blocker, CK_Parts_Price. That is the all or nothing rule. A column in the primary key is blocked the same way, and so is a column in an index. The message names the key or the index.

To see the blockers before you try, ask the catalog. The query lists defaults, indexes and expression dependencies. A check constraint or a computed column shows up in the third part. The last three parts add foreign keys, in both directions, and statistics that you created with CREATE STATISTICS. Each of them blocks a drop as well. The demo table has none of those, so the result shows four rows.

SELECT N'Default constraint' AS DependentKind, dc.name AS DependentName, c.name AS OnColumn
FROM sys.default_constraints AS dc
JOIN sys.columns AS c ON c.object_id = dc.parent_object_id AND c.column_id = dc.parent_column_id
WHERE dc.parent_object_id = OBJECT_ID(N'dbo.Parts')
UNION ALL
SELECT N'Index', i.name, c.name
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 = OBJECT_ID(N'dbo.Parts')
UNION ALL
SELECT CASE ro.type WHEN 'C' THEN N'Check constraint' WHEN 'U' THEN N'Computed column' ELSE ro.type_desc END,
       CASE ro.type WHEN 'U' THEN COL_NAME(d.referencing_id, d.referencing_minor_id) ELSE ro.name END,
       COL_NAME(d.referenced_id, d.referenced_minor_id)
FROM sys.sql_expression_dependencies AS d
JOIN sys.objects AS ro ON ro.object_id = d.referencing_id
WHERE d.referenced_id = OBJECT_ID(N'dbo.Parts') AND d.referenced_minor_id > 0
UNION ALL
SELECT N'Foreign key', fk.name, c.name
FROM sys.foreign_key_columns AS fkc
JOIN sys.foreign_keys AS fk ON fk.object_id = fkc.constraint_object_id
JOIN sys.columns AS c ON c.object_id = fkc.parent_object_id AND c.column_id = fkc.parent_column_id
WHERE fkc.parent_object_id = OBJECT_ID(N'dbo.Parts')
UNION ALL
SELECT N'Foreign key from another table', fk.name, c.name
FROM sys.foreign_key_columns AS fkc
JOIN sys.foreign_keys AS fk ON fk.object_id = fkc.constraint_object_id
JOIN sys.columns AS c ON c.object_id = fkc.referenced_object_id AND c.column_id = fkc.referenced_column_id
WHERE fkc.referenced_object_id = OBJECT_ID(N'dbo.Parts')
UNION ALL
SELECT N'Statistics', st.name, c.name
FROM sys.stats AS st
JOIN sys.stats_columns AS sc ON sc.object_id = st.object_id AND sc.stats_id = st.stats_id
JOIN sys.columns AS c ON c.object_id = sc.object_id AND c.column_id = sc.column_id
WHERE st.object_id = OBJECT_ID(N'dbo.Parts') AND st.user_created = 1
ORDER BY OnColumn, DependentKind;

The same statement can drop the constraints and the columns together. List each constraint after DROP CONSTRAINT, and each column after COLUMN. One statement stays atomic, so nothing is left half done. A DROP TABLE list keeps going after an error, but a DROP COLUMN list is all or nothing.

ALTER TABLE dbo.Parts DROP CONSTRAINT CK_Parts_Price, DF_Parts_Status, COLUMN Price, Status;

Now the table holds PartID and PartName. A dependent index is not part of that list. Drop it with DROP INDEX first, and recreate it afterward if you still need it.

Two Rules at the Edges

DROP COLUMN IF EXISTS needs SQL Server 2016, and it covers only the first column in the list. Repeating IF EXISTS before the second name is a syntax error. The next script uses a three-column table to show both sides. The first statement fails on the second column. The second statement skips the missing first column and drops B.

DROP TABLE IF EXISTS dbo.Tiny;
CREATE TABLE dbo.Tiny (A int NULL, B int NULL, C int NULL);
GO
ALTER TABLE dbo.Tiny DROP COLUMN IF EXISTS B, NoSuchColumn;
GO
ALTER TABLE dbo.Tiny DROP COLUMN IF EXISTS NoSuchColumn, B;

The first statement fails with Msg 4924, because NoSuchColumn doesn’t exist, and B stays. The second statement leaves A and C. The other edge is the last column. Dropping both A and C is refused, because a table needs at least one data column.

ALTER TABLE dbo.Tiny DROP COLUMN A, C;

Msg 4923, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN failed because 'C' is the only data column in table 'Tiny'. A table must have at least one data column.

Why Not One Statement per Column?

You could argue that one statement per column is easier to read and to undo. For two columns that’s true. It also means two schema changes, two schema locks and a half-finished table if the second one fails. Drop multiple columns in one statement when they leave together. To do the same for tables, read Drop Multiple Tables with One DROP TABLE Statement.

What to Remember

List the columns after one DROP COLUMN, and list the blocking constraints after DROP CONSTRAINT in the same statement. Ask the catalog for dependent objects first. Plan a rebuild, because the space comes back only then. Test the statement on a copy, because a dropped column and its data are gone.

Search your code for the column names before you run the statement. A query that still names a dropped column fails with Msg 207, Invalid column name. Views and procedures that read the table are the usual places to look.

When you finish, drop the demo database.

USE master;
GO
IF DB_ID(N'DropColumnsDemo') IS NOT NULL
BEGIN
    ALTER DATABASE DropColumnsDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE DropColumnsDemo;
END;

A dropped column is not a smaller table, it is a metadata change that a rebuild turns into space.

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