Sql Server 批量更新表字段属性

最近需要批量更新表字段属性（如TBZYBRLCXXBQ201、TBZYBRLCXXBQ202、TBZYBRLCXXBQ203……）
由于表太多，所以需要使用升级脚本来批量更新表字段属性（批量增加表字段同理）

 1 USE YXHIS  //选择使用的数据库
 2 GO
 3 DECLARE CurTable CURSOR  //声明游标
 4 FOR 
 5 SELECT name FROM sysobjects where name like 'TBZYBRLCXXBQ%'  
 6 AND TYPE='U'
 7 OPEN CurTable
 8 DECLARE @TBNAME VARCHAR(20)
 9 FETCH NEXT FROM CurTable INTO @TBNAME
10 WHILE (@@FETCH_STATUS <> -1)
11 BEGIN
12    if exists (select * from sysobjects where name=@TBNAME)
13    begin
14      if  exists(select * from syscolumns where name='CHLYS' and id=object_id(@tbname))  //判断是否存在需要更改的列
15      begin
16        EXEC('alter table '+@TBNAME+' alter column CHLYS varchar(30)')
17      end
18    end
19    FETCH NEXT FROM CurTable INTO @TBNAME
20 END
21 CLOSE CurTable
22 DEALLOCATE CurTable
23 GO

 

转载于:https://www.cnblogs.com/yueyingdiaoling/p/7389816.html