It’s frustrated me a few times how difficult it is to drop a column in SQL Server when that column has a default value set on it.  You’ll get an error message like this:

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


If you use SQL Server Management Studio, it takes care of this for you automatically, dropping the default constraint along with the column.  However, if you need to do it programmatically, it’s harder.  The name of the default constraint might have been automatically generated and hard to predict, so you need to look up the default constraint name first, and then drop the column.

Here’s a code snippet that’ll drop a column with a default constraint:


DECLARE @ConstraintName nvarchar(200)
SELECT @ConstraintName = Name FROM SYS.DEFAULT_CONSTRAINTS WHERE PARENT_OBJECT_ID = OBJECT_ID('__TableName__') AND PARENT_COLUMN_ID = (SELECT column_id FROM sys.columns WHERE NAME = N'__ColumnName__' AND object_id = OBJECT_ID(N'__TableName__'))
IF @ConstraintName IS NOT NULL
EXEC('ALTER TABLE __TableName__ DROP CONSTRAINT ' + @ConstraintName)
IF EXISTS (SELECT * FROM syscolumns WHERE id=object_id('__TableName__') AND name='__ColumnName__')
EXEC('ALTER TABLE __TableName__ DROP COLUMN __ColumnName__')

Just replace __TableName__ and __ColumnName__ with the appropriate values.  You can safely run this even if the column has already been dropped.

Bonus:  Here’s the code to drop foreign keys and other types of constraints.


IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where TABLE_NAME = '__TableName__' AND COLUMN_NAME = '__ColumnName__')
BEGIN
SELECT @ConstraintName = CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where TABLE_NAME = '__TableName__' AND COLUMN_NAME = '__ColumnName__'
EXEC('ALTER TABLE __TableName__ DROP CONSTRAINT ' + @ConstraintName)
END