>alter table TBL add TBLJUNK3 char(20) not null default ''
>
>That works fine. Now I want to remove it. I try:
> alter table TBL drop column TBLJUNK3
>and it tells me
> The object 'DF__TBL__TBLJUNK3__084B3915' is dependent on column 'TBLJUNK3'.
>
>I'm assuming that this funny thing is the default it created for this column for me. So I figure I should first try to clear the default before I drop the column, but I can't seem to drop or clear the default.
>
>I tried:
> EXEC sp_unbindefault 'tbl.tbljunk3'
>and it tells me to use ALTER TABLE DROP CONSTRAINT. But I can't get that to work.
>
>I also tried
> alter table TBL alter column TBLJUNK3 drop default
>and of course
> alter table TBL alter column TBLJUNK3 drop constraint
>but all come back with syntax errors.
>
>All I want to do is to delete a stupid column! What is the secret, and how come it's so hard? (Don't feel obliged to answer the last part of that.)
>
>Thanks for your help. Tom
You can drop the constraint first and then the column:
ALTER TABLE tbl DROP CONSTRAINT funny_constraint_name
ALTER TABLE tbl DROP COLUMN tbljunk3
You already know the name of the constraint, but you can get at that with
sp_helpconstraint 'table_name'
HTH