Are you familiar with error message similar like this one?
Msg 5074, Level 16, State 1, Line 1 The object 'DF__Exampl__DateM__61F8A504' is dependent on column 'DateModified'. Msg 4922, Level 16, State 9, Line 1 ALTER TABLE ALTER COLUMN DateModified failed because one or more objects access this column.
Steps you must do in T-SQL code
- get default constraint name
- create alter table command to drop it
- run created command
- alter table to alter your column
- re-create constraint
Typical example, we have column with smalldatetime and we want enhanced it to datetime
Here you have code example for drop column with default value
-- first define variables declare @default sysname, @sql nvarchar(max) -- get name of default constraint select @default = name from sys.default_constraints where parent_object_id = object_id('TABLE_NAME') AND type = 'D' AND parent_column_id = ( select column_id from sys.columns where object_id = object_id('TABLE_NAME') and name = 'COLUMN_NAME' ) -- create alter table command as string and run it set @sql = N'alter table TABLE_NAME drop constraint ' + @default exec sp_executesql @sql -- now we can alter column ALTER TABLE [TABLE_NAME] ALTER COLUMN COLUMN_NAME datetime -- here you can have any datatype you want of course -- last step, we need to recreate constraint -- DEFAULT getdate() is just for example, you can have any constraint you need of course ALTER TABLE [TABLE_NAME] ADD CONSTRAINT [YOUR_CONSTRAINT_NAME] DEFAULT getdate() For COLUMN_NAME
That's it. If you don't know default constraint name you haven't any shorter way how to do it.