SQL修改指定数据库的表数据类型【如将表中所有varchar类型修改为nvarchar】

来源：互联网发布：c语言多if 多else用法编辑：程序博客网时间：2024/05/06 02:43

declare @num int,@tableName varchar(50),@columnName varchar(50),
@typeName varchar(50),@max_length int,@str varchar(500) = ''

declare cur_table cursor for
with cte(num,tableName,columnName,typeName,max_length) as (
select row_number() over(order by ia.name asc) 'Num' ,
ia.name 'tableName',ib.name 'columnName',
ic.name 'typeName',ib.max_length
from test.sys.objects ia
inner join test.sys.columns ib
on ia.object_id = ib.object_id
inner join test.sys.types ic
on ib.user_type_id = ic.user_type_id
where ia.type ='U' and ia.name = 'abc'
) select num,tableName,columnName,typeName,max_length from cte;

open cur_table
fetch next from cur_table into @num,@tableName,
@columnName,@typeName,@max_length
while(@@FETCH_STATUS = 0)
begin
    if(@typeName = 'varchar')
    begin
  set @str = '
  alter table '+@tableName+'   alter column '+@columnName+' nvarchar('+str(@max_length)+')
  '
  exec(@str);
    end
   fetch next from cur_table into @num,@tableName,
   @columnName,@typeName,@max_length
end
close cur_table;
deallocate cur_table;