|
Posted by Hugo Kornelis on 01/02/07 23:06
On 2 Jan 2007 09:19:00 -0800, teddysnips@hotmail.com wrote:
>A few weeks ago a client asked me to add a column to a table so I
>created this script:
>
>ALTER TABLE dbo.tblIndividual ADD fldRenewalStatus BIT NOT NULL
>CONSTRAINT fldRenewalStatus_Default DEFAULT 0
>
>Now they want to change it from a BIT to an INT, to store an enum.
>Fair enough. However, no matter how much I wrangle with a script, I
>can't find a reliable way to alter the column. I've mixed and matched
>the following and nothing seems to work:
Hi Edward,
You can do as Roy suggests, or you can run the following script:
ALTER TABLE dbo.tblIndividual
DROP CONSTRAINT fldRenewalStatus_Default;
ALTER TABLE dbo.tblIndividual
ALTER COLUMN fldRenewalStatus INT NOT NULL;
ALTER TABLE dbo.tblIndividual
ADD CONSTRAINT fldRenewalStatus_Default DEFAULT 0 FOR fldRenewalStatus;
Running the ALTER COLUMN might take long if you have lots of data!
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
[Back to original message]
|