How to DROP DEFAULT constraint?

We initially created a table (SQL Server 2000) having a column with a
DEFAULT value of 0. We want to drop the column, but we cannot until the
DEFAULT constraint is removed. Documentation indicates we should be able to
use the command:
"ALTER TABLE MyTable ALTER COLUMN MyColumn DROP DEFAULT"
but it gives us a syntax error.
Other documentation indicates the "DROP DEFAULT" option is deprecated and we
shouldn't use it.
Can anyone tell me how to do this?Hi
ALTER TABLE...DROP CONSTRAINT......
"Meade Swenson" <mswenson@.e-specs.com> wrote in message
news:u5Q2lzDoGHA.1244@.TK2MSFTNGP05.phx.gbl...
> We initially created a table (SQL Server 2000) having a column with a
> DEFAULT value of 0. We want to drop the column, but we cannot until the
> DEFAULT constraint is removed. Documentation indicates we should be able
> to use the command:
> "ALTER TABLE MyTable ALTER COLUMN MyColumn DROP DEFAULT"
> but it gives us a syntax error.
> Other documentation indicates the "DROP DEFAULT" option is deprecated and
> we shouldn't use it.
> Can anyone tell me how to do this?
>|||Thanks Uri...
Unfortunately, when the DEFAULT contraint was added, it wasn't given a name:
"ALTER TABLE MyTable ADD MyColumn INT DEFAULT 0"
Apparently, the system created its own named constraint which I found in
sysobjects. Am I stuck querying the sysobjects table for the name of the
constraint (based on the table/column names) and then using the retrieved
name in the DROP CONSTRAINT clause?
...mcs
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uvG7W4DoGHA.4124@.TK2MSFTNGP03.phx.gbl...
> Hi
> ALTER TABLE...DROP CONSTRAINT......
> "Meade Swenson" <mswenson@.e-specs.com> wrote in message
> news:u5Q2lzDoGHA.1244@.TK2MSFTNGP05.phx.gbl...
>|||Hi
Yes , run sp_helpconstraint 'tablename'
"Meade Swenson" <mswenson@.e-specs.com> wrote in message
news:OFRZtAEoGHA.4616@.TK2MSFTNGP05.phx.gbl...
> Thanks Uri...
> Unfortunately, when the DEFAULT contraint was added, it wasn't given a
> name:
> "ALTER TABLE MyTable ADD MyColumn INT DEFAULT 0"
> Apparently, the system created its own named constraint which I found in
> sysobjects. Am I stuck querying the sysobjects table for the name of the
> constraint (based on the table/column names) and then using the retrieved
> name in the DROP CONSTRAINT clause?
> ...mcs
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uvG7W4DoGHA.4124@.TK2MSFTNGP03.phx.gbl...
>|||Okay, thanks.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uWngbCEoGHA.4464@.TK2MSFTNGP04.phx.gbl...
> Hi
> Yes , run sp_helpconstraint 'tablename'
> "Meade Swenson" <mswenson@.e-specs.com> wrote in message
> news:OFRZtAEoGHA.4616@.TK2MSFTNGP05.phx.gbl...
>