SQL Server DDL script to add (or delete) the same set of columns for each table in the database?

Question

How to write a sql server DDL script that can:

For each table in the database:

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

Also, if a specific column already exists in the table, just skip that column.

Also, vice versa, for each table, delete these 4 columns.

Answer 1

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 EXISTSto IF EXISTSand ALTER expressions toALTER TABLE...DROP [ColumnName]

Answer 2

, AdaTheDev! , , , aspnet. , .

:

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