In SQL Server, you can create, modify, and remove database objects using a graphical user interface (like using the GUI tools in SQL Server Management Studio), or you can use Transact-SQL to do the same tasks. In fact, the GUI tools actually use T-SQL behind the scenes to perform these tasks.

When using Transact-SQL, you can remove a column from a table by using the ALTER TABLE statement. This statement allows you to change a table’s definition by specifying exactly what changes you require. In our case, we require the removal of a column.

Example

ALTER TABLE Tasks
    DROP COLUMN TaskCode;
GO

This example removes/(drops) the column called TaskCode from the table called Tasks. That’s all we need to do to remove the column.

In this case we also use GO. This isn’t actually part of T-SQL but it is recognised by some utilities to signal the end of a batch of T-SQL statements.

Constraint Error?

In some cases you might get a constraint error when you try to drop a column from a table. If this happens, it’s because the column you’re trying to remove has a constraint that needs to be removed first.

In this case, simply drop the constraint first, then drop the column.

Here’s an example of dropping a constraint, then dropping the table:

-- Drop the constraint
ALTER TABLE Tasks
    DROP CONSTRAINT taskcode_unique;
GO

-- Drop the column
ALTER TABLE Tasks
    DROP COLUMN TaskCode;
GO

This example drops a constraint called taskcode_unique, then it drops the column called TaskCode.