usp_addcolumns

create procedure usp_addcolumns(@tablepattern varchar(100), @missingcolumn sysname, @columndefinition varchar(256), @debug bit=0) as
/*
    name        usp_addcolumns
    author        Wilfred van Dijk (www.wilfredvandijk.nl)
    date        20080527

    purpose        to add a missing column to multiple tables

    parameters    @tablepattern        name of tablecriteria to process
                @missingcolumn        column to add (if not exists)
                @columndefinition    columntype, constraints etc
                @debug                if true, no actual execution of statement (only displayed)

    example        exec usp_addcolumns @tablepattern='ref[_]%', @missingcolumn='createddate', @columndefinition='datetime not null default (getdate())'
                -> adds column createddate to tables starting with 'ref_'

*/begin
    set nocount on

    declare @tn sysname
    declare @sqlcmd nvarchar(512)

    declare c_mantables cursor local fast_forward for
        select    name 
        from    sysobjects
        where    type = 'u'
        and        name like @tablepattern
        and        name not in (select distinct(object_name(id)) from syscolumns where name = @MissingColumn)

    open    c_mantables
    fetch    next 
    from    c_mantables 
    into    @tn

    while @@fetch_status = 0
    begin
        set @SQLCmd = 'alter table ' + @tn + ' add ' + QUOTENAME(@MissingColumn) + ' ' + @ColumnDefinition

        if @debug = 1
            print @SQLCmd
        else
            exec    (@SQLCMD)

        fetch    next 
        from    c_mantables 
        into    @tn
    end

end