【转】动态批量修改字段属性（多表）

我现在有一个数据库，一共一百张表  其中八十张表都有一个相同字段，字段名为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

转自：http://blog.csdn.net/htl258/archive/2009/03/05/3961148.aspx