Sql Server 2008: Dropping Columns with Default Objects

Sql Server 2008 (and I think 2005) they use default objects when you create a column with a default, i.e. alter table MY_TABLE ADD MY_COLUMN varchar(20) default 'HEY' will create an object that specifies the default value.

Generally I couldn't care less about how they handle their default values, but when you try running this alter table MY_TABLE drop column MY_COLUMN you'll get an error along the lines of object DF__MY_TABLE___My_COL__4046A860 depends on that column. See here for more information on Defaults.

The names are created dynamically, unless you specify one of course, so if you're trying to drop a column programatically it can be a problem. Luckily, a few geniuses have come before and have left us some code on how to find and drop these constraints without knowing the name. Those can be found here and here.

These are great but if you have a lot of columns to drop it turns into a lot of code, so here is a stored procedure that can be used to drop the default objects. You are then free to drop the columns as you wish.