sql-server - 如何在 MS SQL 服务器的数据库中的每个表中创建一组相同的列？

Question

我的数据库中有将近 120 个表。最近为了更新一些业务逻辑和控制流量用户，我需要通过添加 5 列来更新每个表。这些列如下所示

1. IsActive
2. IsBlocked
3. IsVerified
4. CreatedByDate
5. LastModifiedByDate

我已经为单个表尝试过这种方式，比如 Users 表

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.columns WHERE table_name = 'Users' AND column_name = 'IsActive')
ALTER TABLE dbo.Users ADD 
 IsActive bit NULL DEFAULT ((1))
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.columns WHERE table_name = 'Users' AND column_name = 'IsBlocked')
ALTER TABLE dbo.Users ADD 
 IsBlocked bit NULL DEFAULT ((1))
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.columns WHERE table_name = 'Users' AND column_name = 'IsVerified')
ALTER TABLE dbo.Users ADD 
 IsVerified  bit NULL DEFAULT ((0))
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.columns WHERE table_name = 'Users' AND column_name = 'CreatedByDate')
ALTER TABLE dbo.Users ADD 
 CreatedByDate datetime NULL DEFAULT GETDATE() 
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.columns WHERE table_name = 'Users' AND column_name = 'LastModifiedByDate')
ALTER TABLE dbo.Users ADD 
 LastModifiedByDate datetime NULL DEFAULT GETDATE() 
GO

但是我真的很难通过替换表名手动申请所有表，因为它包含大约 120 个表。因此，我正在寻找一种最好的方法来最大程度地减少这个冗长的过程或通过最聪明的方式来完成。当我从我的系统表 sys.tables 中获取表列表时，为什么我不通过循环来完成它以最大限度地减少我的工作。谁能帮我解决这个有问题的问题？

Answer 1

你已经完成了一半。现在您只需要动态创建 ALTER 语句，以便从数据库中的表列表中获取表名:

我会这样做:

declare @sql nvarchar(1000)
      , @tableName nvarchar(128) = 'category'
      , @colIsIsActive nvarchar(10) = 'IsActive'

      , @colIsIsBlocked nvarchar(20) = 'IsBlocked'
      , @colIsIsVerified nvarchar(20) = 'IsVerified'
      , @colCreatedByDate nvarchar(20) = 'CreatedByDate'
      , @colIsLastModifiedByDate nvarchar(20) = 'LastModifiedByDate'


select @sql = N' IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.columns WHERE table_name = ''' + @tableName + ''''
             + ' AND column_name = ''' + @colIsIsActive + ''')'
             + ' ALTER TABLE ' + QUOTENAME(@tableName) 
             + ' ADD ' + @colIsIsActive
             + ' BIT NULL DEFAULT ((1))' 

select @sql
--exec(sql)

这需要在游标内写入 5 次，您将表的名称读入 @tableName。您需要为其他 4 列编写完全相同的内容。在这种情况下游标不是问题，您只需运行一次它就会遍历 120 个表，这不是问题。

我会声明 5 个不同的 @sql 变量，以免最后生成太长的字符串。

下面是我正在谈论的整个解决方案:'

declare @sqlAddIsActive nvarchar(1000)
      , @sqlAddIsBlocked nvarchar(1000)
      , @sqlAddIsVerified nvarchar(1000)
      , @sqlAddCreatedByDate nvarchar(1000)
      , @sqlAddLastModifiedByDate nvarchar(1000)

      , @tableName nvarchar(128)  
      , @colIsIsActive nvarchar(10) = 'IsActive'
      , @colIsIsBlocked nvarchar(20) = 'IsBlocked'
      , @colIsIsVerified nvarchar(20) = 'IsVerified'
      , @colCreatedByDate nvarchar(20) = 'CreatedByDate'
      , @colIsLastModifiedByDate nvarchar(20) = 'LastModifiedByDate'


declare tables_cursor cursor for 
select table_name from information_schema.tables where table_type='base table'
open tables_cursor
fetch next from tables_cursor  --start the cursor
into @tableName

while @@fetch_status = 0  --while there is a loaded record, keep processing
begin


select @sqlAddIsActive = N' IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.columns WHERE table_name = ''' + @tableName + ''''
             + ' AND column_name = ''' + @colIsIsActive + ''')'
             + ' ALTER TABLE ' + QUOTENAME(@tableName) 
             + ' ADD ' + @colIsIsActive
             + ' BIT NULL DEFAULT ((1))' 

select @sqlAddIsBlocked = N' IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.columns WHERE table_name = ''' + @tableName + ''''
             + ' AND column_name = ''' + @colIsIsBlocked + ''')'
             + ' ALTER TABLE ' + QUOTENAME(@tableName) 
             + ' ADD ' + @colIsIsBlocked
             + ' BIT NULL DEFAULT ((1))' 

select @sqlAddIsVerified = N' IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.columns WHERE table_name = ''' + @tableName + ''''
             + ' AND column_name = ''' + @colIsIsVerified + ''')'
             + ' ALTER TABLE ' + QUOTENAME(@tableName) 
             + ' ADD ' + @colIsIsVerified
             + ' BIT NULL DEFAULT ((1))' 

select @sqlAddCreatedByDate = N' IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.columns WHERE table_name = ''' + @tableName + ''''
             + ' AND column_name = ''' + @colCreatedByDate + ''')'
             + ' ALTER TABLE ' + QUOTENAME(@tableName) 
             + ' ADD ' + @colCreatedByDate
             + ' BIT NULL DEFAULT ((1))' 

select @colIsLastModifiedByDate = N' IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.columns WHERE table_name = ''' + @tableName + ''''
             + ' AND column_name = ''' + @colIsLastModifiedByDate + ''')'
             + ' ALTER TABLE ' + QUOTENAME(@tableName) 
             + ' ADD ' + @colIsLastModifiedByDate
             + ' BIT NULL DEFAULT ((1))' 

-- to see what is generated: 
select @sqlAddIsActive  
select @sqlAddIsBlocked 
select @sqlAddIsVerified  
select @sqlAddCreatedByDate  
select @sqlAddLastModifiedByDate 

-- to execute, uncomment this after testing the select's
    --exec(@sqlAddIsActive)  
    --exec(@sqlAddIsBlocked)
    --exec(@sqlAddIsVerified)  
    --exec(@sqlAddCreatedByDate)  
    --exec(@sqlAddLastModifiedByDate)   

fetch next from tables_cursor into @tableName  --fetch next record
end

close tables_cursor   --close and deallocate
deallocate tables_cursor

EDIT:

请注意:不要在生产环境中运行。 ALTER TABLE 是一个 DDL 操作，根据表的大小，可能需要一些时间。这需要在开发/测试 环境中进行测试，只有在采取适当措施后才能应用于生产!