SetColumnDefault

            CREATE procedure dbo.SetColumnDefault(@vTableColumnName varchar(257), @vDefVal varchar(50))
            AS
            /*  
                DPC 2.18.2003 - This procedure takes a table.column argument and sets that column's
                         default value to vDefVal by dropping its default constraint and
                        then re-adding it with the new default value.
            */
            declare @vConstraintName varchar(128)
            declare @vTableName varchar(128)  
            declare @vColumnName varchar(128)
            declare @vCollation varchar(128)

            if charindex('.',@vTableColumnName) = 0 or charindex('dbo',@vTableColumnName) > 0
                begin
                    raiserror ('vTableColumnName must have the form table.column',16,1)
                end
            else
                begin
                set @vTableName = parsename(@vTableColumnName, 2)
                set @vColumnName = parsename(@vTableColumnName, 1)

                set nocount on

                select  @vCollation = c.collationid from syscolumns c
                    where c.id = object_id(@vTableName)        
                    and c.name = @vColumnName    

                select @vConstraintName = o.name from syscolumns c, sysobjects o
                    where c.id=object_id(@vTableName)
                    and c.cdefault = o.id
                    and c.name = @vColumnName

                IF @vConstraintName IS NOT NULL
                    BEGIN
                    execute ('ALTER TABLE ' + @vTableName + ' DROP CONSTRAINT ' + @vConstraintName) 
                      END
                ELSE
                    BEGIN
                    SET @vConstraintName = 'DF__' + left(@vTableName,12) + '__' + left(@vColumnName,12)
                    END

                IF @vDefVal IS NOT NULL
                    BEGIN
                    --if the default value is a text variable, we need to wrap it in quotes
                    IF @vCollation IS NOT NULL
                        BEGIN
                        set @vDefVal = '''' + @vDefVal + ''''            
                        END

                    execute ('ALTER TABLE ' + @vTableName + ' ADD CONSTRAINT ' +  @vConstraintName + ' default (' + @vDefVal + ') for ' + @vColumnName )
                    END
            END