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...
>> 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
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...
>> 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?
>>
>|||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...
>> 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...
>> 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?
>>
>>
>