SQL Alter a column with a default value

If a column has a default value it means there is a constraint on that column setting it the default value.  The alter command will give you an error like this:


the object is dependent on column .


I found a script for altering several columns in different tables, which alter columns from float to decimal, and all of them had default value=0.


DECLARE @col nvarchar(100)
DECLARE @tabel nvarchar(100)
DECLARE @constr nvarchar(100)
declare @sql nvarchar(500)


DECLARE cTemp CURSOR LOCAL FOR select t_obj.name as TABLE_NAME
,col.name as COLUMN_NAME
,c_obj.name as CONSTRAINT_NAME
from sysobjects c_obj
join syscomments com on c_obj.id = com.id
join sysobjects t_obj on c_obj.parent_obj = t_obj.id
join    sysconstraints con on c_obj.id = con.constid
join syscolumns col on t_obj.id = col.id
and con.colid = col.colid where col.name=’col1' or col.name=’col2'  –etc


OPEN cTemp


FETCH NEXT FROM cTemp INTO @tabel, @col, @constr
WHILE @@FETCH_STATUS = 0
BEGIN


SET @sql = ‘ALTER TABLE ‘+@tabel+’ DROP CONSTRAINT ‘+ @constr
PRINT @sql
EXEC sp_executesql @sql


SET @sql = ‘ALTER TABLE ‘+@tabel+’ ALTER COLUMN ‘+@col+’ DECIMAL(14, 6)’
PRINT @sql
EXEC sp_executesql @sql


SET @sql=’alter table ‘+@tabel+’ ADD CONSTRAINT ‘+@constr+’  DEFAULT 0 FOR ‘+ @col
PRINT @sql
EXEC sp_executesql @sql


FETCH NEXT FROM cTemp INTO @tabel, @col, @constr
END
CLOSE cTemp
DEALLOCATE cTemp


This script will drop the constraints for these columns, alter the columns and then rebuild the constraints. In the select from the cursor you can include the column with the default value, if you need to modify the script for columns with different default values.


Ref: http://kb.softescu.ro/programming/p-tsql/sql-alter-a-column-with-a-default-value/