Add columns to SQL server tables on the fly

Sunday, January 20, 2008 11:43 PM

Here is a handy script that I usually run after designing a database. My database designs are usually consistent and they are made up of certain fields/columns that exist on all tables. Ideally, in my world all tables should have an "IsDeleted/bit" column and some auditing columns such as "CreatedOn", "ModifiedOn", "CreatedBy" and "ModifiedBy". To reduce the time it takes to design these tables, I always ignore those columns and run this script after I'm done with the design. The script simply loops through all the tables in the database and determines whether or not it has the required columns, if not it will add them as necessary. This is a dirty script and easily modified to add other columns, constraints, etc...

-- Declare all some variables
declare @databaseName varchar(max),
        @tableschema varchar(max),
        @tabletype varchar(50)

-- Set the variable values
set @databaseName = 'Northwind';
set @tableSchema = 'dbo';
set @tabletype = 'base table';

-- Grab a list of all tables from the database schema
With Tables AS
(
    select row_number() over(order by table_Name desc) as RowId, 
        table_name from information_schema.tables
        where table_catalog = @databaseName 
            and table_schema = @tableschema and table_type = @tabletype
)select * into #temptables from Tables


-- More variables and initialization
declare @cnt int, 
        @maxcnt int, 
        @tableName varchar(max), 
        @found int, 
        @sql nvarchar(max)

set @cnt = 1
set @maxcnt = (select max(rowid) from #temptables)

-- Loop through the list and use row id as our guide and limit
while(@cnt <= @maxcnt)
begin
    set @tableName = (select table_name from #temptables where rowid = @cnt)
    set @sql = '';
    Print 'Processing Row Id: ' + cast(@cnt as varchar) + ', Table: ' + @tableName
    
    -- Check for IsDeleted
    set @found = (select count(column_name) from information_schema.columns where table_catalog = @databaseName 
            and table_name = @tableName and column_name = 'IsDeleted')
    if(@found <= 0) set @sql = @sql + 'alter table [' + @tableName + '] add [IsDeleted] [bit] NOT NULL DEFAULT ((0));'

    -- Check for CreatedOn
    set @found = (select count(column_name) from information_schema.columns where table_catalog = @databaseName 
            and table_name = @tableName and column_name = 'CreatedOn')
    if(@found <= 0) set @sql = @sql + 'alter table [' + @tableName + '] add [CreatedOn] [datetime] NOT NULL DEFAULT (getdate());'

    -- Check for ModifiedOn
    set @found = (select count(column_name) from information_schema.columns where table_catalog = @databaseName 
            and table_name = @tableName and column_name = 'ModifiedOn')
    if(@found <= 0) set @sql = @sql + 'alter table [' + @tableName + '] add [ModifiedOn] [datetime] NOT NULL DEFAULT (getdate());'

    -- TODO: Exclude as you see fit
    if(@tableName <> 'Users' and right(@tableName, 5) <> 'Types' and right(@tableName, 8) <> 'statuses' 
        and right(@tableName, 8) <> 'Networks' and right(@tableName, 15) <> 'Classifications')
    begin
        -- Check for CreatedBy
        set @found = (select count(column_name) from information_schema.columns where table_catalog = @databaseName 
                and table_name = @tableName and column_name = 'CreatedBy')
        if(@found <= 0) set @sql = @sql + 'alter table [' + @tableName + '] add [CreatedBy] [int] NOT NULL;'

        -- Check for modifiedBy
        set @found = (select count(column_name) from information_schema.columns where table_catalog = @databaseName 
                and table_name = @tableName and column_name = 'modifiedBy')
        if(@found <= 0) set @sql = @sql + 'alter table [' + @tableName + '] add [ModifiedBy] [int] NOT NULL;'
    end

    if(len(@sql) >0)
    begin
        exec sp_executesql @sql
        Print cast(@sql as varchar(max))
    end

    Print ''

    set @cnt = @cnt + 1
    set @found = 0
end

drop table #temptables