Sql server will not let you dropa table if it has default contraints (like default values for columns.)
This script will let you find them.
1. Determine the constraint name.

if object_id('[dbo].[GetDefaultConstraintName]') is not null
    drop function [dbo].[GetDefaultConstraintName]
go

create function [dbo].[GetDefaultConstraintName] (
    @TableName varchar(max),
    @ColumnName varchar(max))
returns varchar(max)
as
begin

    -- Returns the name of the default constraint for a column

    declare @Command varchar(max)
    select 
        @Command = d.name
    from 
        ((
        sys.tables t join
        sys.default_constraints d
            on 
                d.parent_object_id = t.object_id) join
        sys.columns c
            on 
                c.object_id = t.object_id and 
                c.column_id = d.parent_column_id)
    where 
        t.name = @TableName and 
        c.name = @ColumnName
    return @Command
end
go

2. Generate dynamic sql to drop the constraint. Assume that the table name is MyTable and the column name is MyColumn.
execute ('alter table MyTable drop constraint '+
           [dbo].[GetDefaultConstraintName]('MyTable','MyColumn'))

3. Re-create the constraint with the desired default value. Assume that the new default value should be 33.
alter table MyTable add constraint [DF_MyTable_MyColumn] default (33) for MyColumn
1

View comments

About Me
About Me
My Photo
Piscataway, New Jersey, United States
I am an electrical engineer by education and a software developer by profession. I like building electro-mechanical models. I also like grilling and barbecuing with passion. To burn my beer cals, I swim and run. I usually post my DIY projects on: http://www.instructables.com/member/kabira/
Blog Archive
Loading
Dynamic Views theme. Powered by Blogger.