SQL Server DDL script to append (or drop) the same set of columns for every table in a database?

How would one write a sql server DDL scri开发者_开发知识库pt that can:

For each table in database:

add column CreatedBy
add column CreateDate
add column UpdatedBy
add column UpdatedDate

Also, if the particular column already exists on the table, just skip that column.

Also, the reverse of it, for each table, drop those 4 columns.

DECLARE @TableName NVARCHAR(128)

DECLARE curTables CURSOR FAST_FORWARD FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

OPEN curTables

FETCH NEXT FROM curTables INTO @TableName

WHILE (@@FETCH_STATUS = 0)
    BEGIN
        IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@TableName AND COLUMN_NAME='CreatedBy')
            EXECUTE ('ALTER TABLE [' + @TableName + '] ADD CreatedBy VARCHAR(50)')

        IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@TableName AND COLUMN_NAME='CreatedDate')
            EXECUTE ('ALTER TABLE [' + @TableName + '] ADD CreatedDate DATETIME')

        IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@TableName AND COLUMN_NAME='UpdatedBy')
            EXECUTE ('ALTER TABLE [' + @TableName + '] ADD UpdatedBy VARCHAR(50)')

        IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@TableName AND COLUMN_NAME='UpdatedDate')
            EXECUTE ('ALTER TABLE [' + @TableName + '] ADD UpdatedDate DATETIME')


        FETCH NEXT FROM curTables INTO @TableName   
    END

CLOSE curTables
DEALLOCATE curTables

Then the opposite is just a case of changing IF NOT EXISTS to IF EXISTS and the ALTER statements to ALTER TABLE...DROP [ColumnName]

Great sample, AdaTheDev! However, I made a small change to it to ignore tables in various schemas, views and aspnet membership tables. You can easily chan ge it if you want to just process tables in a certain schemas.

Here's my version:

DECLARE @TableName NVARCHAR(128)
DECLARE @TableSchema NVARCHAR(128)

DECLARE curTables CURSOR FAST_FORWARD FOR
select TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE='BASE TABLE' and (TABLE_SCHEMA not in ('schems1', 'schems2') and  TABLE_NAME not like 'aspnet_%')
OPEN curTables

FETCH NEXT FROM curTables INTO @TableSchema, @TableName

WHILE (@@FETCH_STATUS = 0)
    BEGIN
        IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@TableName AND COLUMN_NAME='Locked')
            EXECUTE ('ALTER TABLE  [' + @TableSchema + '].[' + @TableName + '] ADD Locked bit NOT NULL CONSTRAINT DF_' + @TableSchema + '_' + @TableName + '_Locked DEFAULT 0')

        FETCH NEXT FROM curTables INTO @TableSchema, @TableName
    END

CLOSE curTables
DEALLOCATE curTables