Can I do an "alter table add column", with an existing named default?

Hello folks,
Shouldn't I be able to do this? I'm not able to get the syntax to work
Alter table TableName add ColumnName tinyint not null default
DefaultZero
This works:
Alter table TableName add ColumnName tinyint not null default (0)
...but I need to immediately drop the default programatically, and for
that I need the default name, so I'd like to name it myself.
I tried this:
EXEC sp_unbindefault 'TableName .ColumnName'
but got the error "Cannot unbind from 'TableName .ColumnName'. Use
ALTER TABLE DROP CONSTRAINT.
I guess I could query the system tables to figure out the name, but
would prefer not to if I can avoid it.
thanks for any ideas!
SylviaHere's an example with the proper syntax:
ALTER TABLE TableName
ADD ColumnName tinyint NOT NULL
CONSTRAINT DF_TableName_ColumnName DEFAULT 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Sylvia" <Puget4753@.yahoo.com> wrote in message
news:1116288687.747349.22520@.g47g2000cwa.googlegroups.com...
> Hello folks,
> Shouldn't I be able to do this? I'm not able to get the syntax to work
> Alter table TableName add ColumnName tinyint not null default
> DefaultZero
> This works:
> Alter table TableName add ColumnName tinyint not null default (0)
> ...but I need to immediately drop the default programatically, and for
> that I need the default name, so I'd like to name it myself.
> I tried this:
> EXEC sp_unbindefault 'TableName .ColumnName'
> but got the error "Cannot unbind from 'TableName .ColumnName'. Use
> ALTER TABLE DROP CONSTRAINT.
> I guess I could query the system tables to figure out the name, but
> would prefer not to if I can avoid it.
> thanks for any ideas!
> Sylvia
>|||thanks - this works perfectly!