|
|
Posted by Gert-Jan Strik on 07/24/06 11:11
Giacomo,
You must use ALTER TABLE ... DROP CONSTRAINT to drop the column default.
Another option (than the one posted by Erland) is to recreate the
column. Note that this will only work if there is no DRI on the column,
and no indexes. For example:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
EXEC sp_rename 'PlanningDettaglio.OreStraordinario', '__old', 'COLUMN'
ALTER TABLE PlanningDettaglio
ADD OreStraordinario DECIMAL(18,2) NULL -- or whatever the original
definition
UPDATE PlanningDettaglio
SET OreStraordinario = __old
ALTER TABLE PlanningDettaglio DROP COLUMN __old
COMMIT TRANSACTION
HTH,
Gert-Jan
Giacomo wrote:
>
> Hi,
> I've the following problem. I must delete a column DEFAULT from a table,
> but I must do it with a script, independently from the server where it'll
> be executed.
> Locally I've tried with:
>
> ALTER TABLE [dbo].[PlanningDettaglio]
> DROP CONSTRAINT [DF__PlanningD__OreSt__688C6DAC]
> GO
>
> ALTER TABLE [dbo].[PlanningDettaglio]
> ALTER COLUMN [OreStraordinario] varchar(5)
> GO
>
> ALTER TABLE [dbo].[PlanningDettaglio]
> ADD DEFAULT ('00.00') FOR [OreStraordinario]
> GO
>
> it works, but only locally.
> I've tried with:
>
> ALTER TABLE PlanningDettaglio ALTER COLUMN OreStraordinario DROP DEFAULT
>
> Err.: Incorrect syntax near the keyword 'DEFAULT'.
>
> Can someone help me please?
>
> Thanks in advance,
> Giacomo
>
> P.S.
> We're using SQL Sever 2000 (version 8.00.194)
Navigation:
[Reply to this message]
|