动态批量修改字段属性

原帖：http://blog.youkuaiyun.com/htl258/archive/2009/03/05/3961148.aspx

 

我现在有一个数据库，一共一百张表  其中八十张表都有一个相同字段，字段名为IP，但是各个表中的IP字段属性不一定相同。  现在我要将这80个IP字段都变成 varchar(20) default('127.0.0.1') not null。

declare @sql nvarchar ( 4000 )
select @sql = ''
select @sql = @sql + ' alter table ' + a.name + ' alter column IP varchar(20)  not null; ' from sysobjects a join syscolumns b on a.id = b.id
where b.name = ' IP ' and a.type = ' u '
exec ( @sql )
select @sql = ''
select @sql = @sql + ' alter table ' + a.name + ' add default( '' 127.0.0.1 '' ) for IP ; ' from sysobjects a join syscolumns b on a.id = b.id
where b.name = ' IP ' and a.type = ' u '
exec ( @sql )

---------------------

declare @ObjName varchar ( 100 )
Declare MyCur Cursor Local Read_Only Fast_Forward For  
select a.name  from sysobjects a,syscolumns b where a.id = b.id and b.name = ' IP ' and a.xtype = ' u '  
Open   MyCur 
Fetch Next From MyCur Into @ObjName  
While @@Fetch_Status = 0   
Begin  
    exec ( ' alter table ' + @ObjName + ' drop column  IP ' )
    exec ( ' alter table ' + @ObjName + ' add  IP varchar(20) default( '' 127.0.0.1 '' )  not null ' )
    Fetch Next From MyCur Into @ObjName  
End  
Close MyCur 
Deallocate MyCur