批量修改DB中字段类型代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】批量修改DB中字段类型

作者:zhaowenzhong / 发布于2013/3/26/ 717


	---------这里举例 varchar 转 nvarchar 类型

	---------将DB中 字段为varchar(10)的改为nvarchar(20)
	---------字段为varchar(20)的改为nvarchar(40)

	declare @tb_name Nvarchar(120)
	declare @column_name Nvarchar(120)
	declare @max_len int
	declare @sql nvarchar(max)

	
	declare cur_cur cursor for
	select b.name, a.name as column_name ,a.max_length from sys.all_columns a join sys.sysobjects b on a.object_id=b.id and b.xtype='U'
	join sys.types c on a.user_type_id=c.user_type_id and c.name ='varchar' and c.user_type_id =167 and (a.max_length =10 or a.max_length =20)
	open cur_cur
	fetch next from cur_cur into @tb_name,@column_name,@max_len
	while @@FETCH_STATUS =0
	begin
	if @max_len =10
	 begin
	 set @sql=N'alter table '+@tb_name+' alter column'+@column_name+' nvarchar(20)'
	 end
	if @max_len=20
	 begin
	 set @sql=N'alter table '+@tb_name+' alter column'+@column_name+' nvarchar(40)'
	 end
	EXEC SP_EXECUTESQL @SQL
	fetch next from cur_cur into @tb_name,@column_name,@max_len
	end
	close cur_cur
	deallocate cur_cur

试试其它关键字

　字段类型　

同语言下

可能有用的

zhaowenzhong贡献的其它代码(7)