Automatically Sets Default on Columns

/*

Procedure Name: sp_SetDefaults

Written by Michael EspositoJuly 21, 2004

Parameters:
@table_name -- Name of the table
@column_name -- Name of the column to apply the default to

Description:
If you set up a default on a column AFTER data has been entered, then this procedure will apply
the default to all NULL values within that column. 

Limitations: 
It only works with numeric values right now.

*/
alter procedure sp_SetDefaults (@table_name varchar(1000), @column_name varchar(1000))
as
declare @sql varchar(4000)

begin transaction

set @table_name = rtrim(@table_name)
set @column_name = rtrim(@column_name)

set @sql = 'declare @type varchar(10)

select @type = data_type FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME =  ''' + @table_name  + ''' and column_name = ''' + @column_name + '''

update ' + @table_name + ' set ' + @column_name + ' = (SELECT  column_default = case
when column_default like ''(%)'' and @type = ''int'' then convert(int , substring(column_default, 2, dbo.instr(column_default, '')'', 1, 1)-2))
when column_default like ''(%)'' and @type = ''float'' then convert(float , substring(column_default, 2, dbo.instr(column_default, '')'', 1, 1)-2))
when column_default like ''(%)'' and @type = ''real'' then convert(real , substring(column_default, 2, dbo.instr(column_default, '')'', 1, 1)-2))
when column_default like ''(%)'' and @type = ''money'' then convert(money , substring(column_default, 2, dbo.instr(column_default, '')'', 1, 1)-2))
else column_default
end
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME =  ''' + @table_name  + ''' and column_name = ''' + @column_name + ''') where ' + @column_name + ' is null'

exec (@sql)

if @@error <> 0
begin
raiserror('Error Occured in sp_SetDefaults', 16, 1)
rollback transaction
return
end

commit transaction

GO