How to Drop a Column in SQL | Syntax for Every Major Database

To remove a column from an existing table, use ALTER TABLE followed by DROP COLUMN and the column name, though the exact syntax varies by database system.

This guide covers how to drop a column in SQL across every major database system. The core command is ALTER TABLE … DROP COLUMN, but MySQL, SQL Server, PostgreSQL, IBM Db2, CockroachDB, QuestDB, and Oracle each add their own syntax quirks and rules around multiple columns, dependencies, and what happens to disk space afterward. The table below shows the exact command each database expects.

The Universal Command Pattern

Every major SQL database that supports column removal uses the same basic structure: ALTER TABLE table_name DROP COLUMN column_name;. The COLUMN keyword is optional in some systems like MySQL but required in others. A successful run typically returns Query OK or a similar confirmation message, and the column along with all its data is permanently removed.

SQL Server 2016 and later adds a safety option: ALTER TABLE table_name DROP COLUMN IF EXISTS column_name;, which silently skips the operation when the column doesn’t exist instead of throwing an error. The standard form without the IF EXISTS clause works across all supported databases.

Dropping a Column in SQL: Syntax Across Every Major Database

Different databases add their own rules around optional keywords, multiple-column support, and dependency handling. The table below shows the exact syntax for each system, based on the official documentation for each engine.

Dropping Multiple Columns in One Statement

The ability to drop multiple columns in a single ALTER TABLE statement saves time on schema changes, but the syntax differs across databases. MySQL 9.7 documents the form ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;, repeating DROP COLUMN for each column. SQL Server allows listing multiple columns in the form ALTER TABLE t DROP COLUMN c, d; without repeating DROP COLUMN. CockroachDB and PostgreSQL also support dropping multiple columns in one statement, following the PostgreSQL convention. Always check your database’s documentation before running a multi-column drop — using the wrong syntax produces an error.

For MySQL, if the session has sql_safe_updates enabled, the drop will be blocked. Disable it with SET sql_safe_updates = false; before running the ALTER TABLE statement, then re-enable it afterward.

What to Check Before Dropping a Column

Dropping a column is destructive because it permanently removes the column and all of its data. Before running the command, check whether the column is referenced by any constraints, foreign keys, indexes, or views. SQL Server returns an error if a column has dependencies. CockroachDB supports the RESTRICT keyword, which prevents the drop when any dependent objects exist. IBM Db2 requires that the table is not a typed table — if it is, the operation is not allowed.

Make a backup or take a snapshot of the table before dropping columns in production. The operation cannot be rolled back once committed.

What Happens to Disk Space When You Drop a Column?

Disk space is not always reclaimed immediately after dropping a column. In many database systems, the drop operation is primarily a metadata change — it marks the column as removed but does not always compact the underlying storage. SQL Server, for example, removes the column logically while the physical storage may remain until you rebuild the table or run index maintenance. In QuestDB, the drop attempts to remove the column’s files from all partitions, making space reclamation more direct. CockroachDB reclaims storage during its normal garbage collection cycles. If freeing disk space quickly matters, plan for a table rebuild or maintenance operation after the drop, and check your database’s specific behavior rather than assuming the space vanishes immediately.

Common Mistakes and How to Fix Them

The most common mistakes when dropping a column come from syntax differences between databases, dependency oversights, and incorrect assumptions about what the command actually does to storage. The table below covers the top pitfalls and how to avoid them.

Quick Reference: Drop Column Syntax by Database

The core command across every major database is the same: ALTER TABLE table_name DROP COLUMN column_name;. MySQL lets you omit the COLUMN keyword. SQL Server adds IF EXISTS as a safety net in version 2016 and later. Multi-column drops require checking your specific database’s syntax — MySQL repeats DROP COLUMN per column, while SQL Server and PostgreSQL use a comma-separated list. And remember: the space the column occupied is not freed instantly in most engines, so budget time for a maintenance pass if you need the storage back.