[ACCEPTED]-How to modify datatype of a column with a default value-alter

Accepted answer
Score: 25

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
Score: 2

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.

Score: 0

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