[ACCEPTED]-How to modify datatype of a column with a default value-alter
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.
More Related questions