批量修改同一个字段的数据类型

-- 批量修改所有表中字段名为 aaa 的字段类型

-- 要保证字段是可以修改的

declare @sql varchar(1000)

declare cur_sql cursor for

select "alter table " + a.name + " alter column " + b.name + " varchar(20) "

from sys.objects a,sys.columns b

where a.object_id = b.object_id and b.name = "aaa" and a.type = "U"

open cur_sql

fetch cur_sql into @sql

while @@fetch_status = 0

begin

-- 每次修改

execute ( @sql )

fetch cur_sql into @sql

end

close cur_sql

deallocate cur_sql

-- 还有一种方案是把所有的语句 取 出来，然后再运行

-- 如下，把结果集里的语句copy到查询分析器里

select "alter table " + a.name + " alter column " + b.name + " varchar(20) "

from sys.objects a,sys.columns b

where a.object_id = b.object_id and b.name = "aaa" and a.type = "U"

所属专题:

如果您觉得本文或图片不错，请把它分享给您的朋友吧！