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:
- get default constraint name
- create alter table command to run
- run created command
- 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?