[ACCEPTED]-How to modify datatype of a column with a default value-alter
Accepted answer
You need to do this in several steps - first: drop 3 the default constraint on your column, then 2 modify your column.
You could use code something 1 like this:
-- find out the name of your default constraint -
-- assuming this is the only default constraint on your table
DECLARE @defaultconstraint sysname
SELECT @defaultconstraint = NAME
FROM sys.default_constraints
WHERE parent_object_id = object_ID('dbo.mytable')
-- declare a "DROP" statement to drop that default constraint
DECLARE @DropStmt NVARCHAR(500)
SET @DropStmt = 'ALTER TABLE dbo.mytable DROP CONSTRAINT ' + @defaultconstraint
-- drop the constraint
EXEC(@DropStmt)
-- alternatively: if you *know* the name of the default constraint - you can do this
-- more easily just by executing this single line of T-SQL code:
-- ALTER TABLE dbo.mytable DROP CONSTRAINT (fill in name of constraint here)
-- modify the column's datatype
ALTER TABLE dbo.mytable
Alter Column myColumn smallint NOT NULL
-- re-apply a default constraint - hint: give it a sensible name!
ALTER TABLE dbo.mytable
ADD CONSTRAINT DF_mytable_myColumn DEFAULT 1 FOR MyColumn
You could do it as a three step process
- add the new column with a different name,
- copy the values from the old column to the new
- drop the old column
It 2 it matters that the name is the same, then 1 repeat the process to change the name back.
You can find the constraint name for the 6 default using MS Management Studio. Just 5 find the tables folder for the given DB 4 and look under Constraints. If there are 3 many constraints, you can "Script the Constraint(s) to 2 a query window which show the associated 1 column name.
Source:
stackoverflow.com
More Related questions
Cookie Warning
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.