Automatically Sets Default on Columns
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.
You will also need the INSTR function, which you can also get from this site.
This is my first real attempt at making a universally useful script, so any constructive comments/suggestions would be most appreciated.
/*
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