SQL Server DDL 脚本为数据库中的每个表追加(或删除)同一组列？

Question

如何编写一个 sql server DDL 脚本可以:

对于数据库中的每个表:

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

此外，如果表中已存在特定列，则跳过该列。

另外，反过来，对于每个表，删除这 4 列。

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

那么相反的情况就是将 IF NOT EXISTS 更改为 IF EXISTS 并将 ALTER 语句更改为 ALTER TABLE...DROP [ColumnName]