When you try drop column in MSSQL 2005 with some default value by ALTER TABLE xxx DROP column_name you get error about existing constraint. Unfortunately there is nothing like CASCADE CONSTRAINT. You must DROP constraint first and then you can DROP column. I will show you the easiest way, how to do it.

Are you familiar with error message similar like this one?

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

Steps you must do in T-SQL code:

1. get default constraint name
2. create alter table command to run
3. run created command
4. alter table for drop you column

Here you have code example for drop column with default value

-- first define variables
declare @default sysname, @sql nvarchar(max)
-- get name of default constraint
select @default = name 
from sys.default_constraints 
where parent_object_id = object_id('TABLE_NAME')
AND type = 'D'
AND parent_column_id = (
    select column_id 
	from sys.columns 
    where object_id = object_id('TABLE_NAME')
	and name = 'COLUMN_NAME'
)
-- create alter table command as string and run it
set @sql = N'alter table TABLE_NAME drop constraint ' + @default
exec sp_executesql @sql
-- now we can finally drop column
ALTER TABLE [TABLE_NAME]
DROP COLUMN COLUMN_NAME 

And that's it. Not so ugly right?

Tweet