How to modify datatype of a column with a default value

Question

I'm trying to change the datatype of a column in SQL Server from tinyint to smallint.

But there's a default value on my column and I don't know the name of the constraint.

Is there an easy way to do it ?

This don't work because of the default constraint :

ALTER TABLE mytable
Alter Column myColumn smallint NOT NULL default 1

score 25 · Accepted Answer · answered Feb 15 '12 at 19:50

You need to do this in several steps - first: drop the default constraint on your column, then modify your column.

You could use code something 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 should probably filter the constraint name select to column name, in case there are multiple default constraints for the table. `SELECT @defaultconstraint = d.NAME FROM sys.default_constraints AS d INNER JOIN sys.columns AS c ON d.parent_object_id = c.object_id AND d.parent_column_id = c.column_id WHERE d.parent_object_id = Object_id('dbo.mytable') AND c.NAME = 'myColumn '` — Almighty, May 07 '20 at 07:41

score 2 · Answer 2 · answered Feb 15 '12 at 19:45

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 it matters that the name is the same, then repeat the process to change the name back.

answered Feb 15 '12 at 19:45

Matt T

607
3
10

Yeah could be a solution but if i have a lot of data it would not be great – GregM Feb 15 '12 at 19:47

score 0 · Answer 3 · answered Feb 15 '12 at 19:50

You can find the constraint name for the default using MS Management Studio. Just find the tables folder for the given DB and look under Constraints. If there are many constraints, you can "Script the Constraint(s) to a query window which show the associated column name.

How to modify datatype of a column with a default value

3 Answers3