批量修改数据库中字段的数据类型

来源：互联网发布：淘宝同学官网编辑：程序博客网时间：2024/05/16 05:36

在有些时候，需要将某种数据类型的字段，修改为另一种数据类型，可使用下列脚本实现；例如：原来定义为decimal(18,2)类型的所有统一修改为decimal(19,4)。
– 关闭约束
declare tb cursor for
SELECT sql=’alter table [‘+d.name+’] NOCHECK CONSTRAINT all’
FROM syscolumns a left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype=’U’ and d.name<>’dtproperties’
where b.name in(‘decimal’) GROUP BY d.name
declare @sql1 varchar(1000)
open tb
fetch next from tb into @sql1
while @@fetch_status = 0
begin
print @sql1
exec(@SQL1)
fetch next from tb into @sql1
end
close tb
deallocate tb

-- 修改字段数据类型

declare tb cursor for
SELECT sql=’alter table [‘+d.name+’] alter column [‘+a.name+’] decimal(19,4)’
FROM syscolumns a left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype=’U’ and d.name<>’dtproperties’
where b.name in(‘decimal’) order by d.name,a.name
declare @sql2 varchar(1000)
open tb
fetch next from tb into @sql2
while @@fetch_status = 0
begin
print @sql2
exec(@SQL2)
fetch next from tb into @sql2
end
close tb
deallocate tb

-- 恢复 约束

declare tb cursor for
SELECT sql=’alter table [‘+d.name+’] CHECK CONSTRAINT ALL’
FROM syscolumns a left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype=’U’ and d.name<>’dtproperties’
where b.name in(‘decimal’) GROUP BY d.name
declare @sql3 varchar(1000)
open tb
fetch next from tb into @sql3
while @@fetch_status = 0
begin
print @sql3
exec(@SQL3)
fetch next from tb into @sql3
end
close tb
deallocate tb

0 0