批量修改表中数据类型

需求：把数据库中所有’varchar’,‘char’类型修改成’nvarchar’,'nchar’类型，不可能一个一个去修改，在网上查找相关资料，整合成如下脚本。
思路：查询出所有要修改的字段，构造修改脚本，如果字段为约束或索引，则不允许修改，需先删除约束索引，执行修改脚本，然后再创建约束和索引。

--游标
declare AlterColumn cursor for 
--查出所有需要修改的字段 构造要修改的sql脚本
SELECT d.name as TBName,a.name as ColumnName,
'alter table ['+d.name+'] alter column ['+a.name+'] n'+b.name+'('+cast(a.length as CHAR(4))+')'
+case a.isnullable when 1 then ' null' else ' not null' end AS AlterSql
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('varchar','char') 
order by d.name,a.name

declare @TBName         varchar(50)    --表名
declare @ColumnName     varchar(50)    --字段名
declare @AlterSql       varchar(1000)  --需要修改的sql脚本
declare @iCount         int            --修改次数
set @iCount=0
open AlterColumn 
fetch next from AlterColumn into @TBName,@ColumnName,@AlterSql 
while @@fetch_status = 0 
begin   
    --如果修改列为约束索引或是设置默认值 不允许修改 所以先删除 修改之后 再创建 
    declare @ConstraintIndex    nvarchar(max) --约束索引脚本
    declare @DefaultScript      nvarchar(max) --默认值脚本
    declare @Sql                nvarchar(max) --动态SQL命令
    --------------------约束索引---------------------------------------
    if object_id('tempdb..#tTempConstraintIndex') is not null drop table #tTempConstraintIndex
    create table #tTempConstraintIndex
    (
		NAME             nvarchar(50)   null,  --约束索引名
		UNIQ             nvarchar(50)   null,  --约束索引类型
		ConstraintIndex  nvarchar(200)  null,  --约束索引脚本
    )
	set @Sql='
	insert into #tTempConstraintIndex(NAME,UNIQ,ConstraintIndex)
	select NAME,UNIQ,
	case when UNIQ=''PRIMARY KEY'' 
	then ''ALTER TABLE ''+TABNAME+'' ADD CONSTRAINT ''+name+'' PRIMARY KEY ''+cluster+'' (''+COLNAME+'')''
	when UNIQ=''UNIQUE'' 
	then ''ALTER TABLE ''+TABNAME+'' ADD CONSTRAINT ''+name+'' UNIQUE ''+cluster+'' (''+COLNAME+'')''
	when UNIQ=''INDEX''     
	then ''CREATE ''+cluster+'' INDEX ''+name+'' ON ''+TABNAME+'' (''+COLNAME+'')''
	end 
	from(  
	SELECT A.INDID,B.KEYNO,NAME,
	''dbo.''+(SELECT NAME FROM SYSOBJECTS WHERE ID=A.ID) AS TABNAME,
	(SELECT NAME FROM SYSCOLUMNS WHERE ID=B.ID AND COLID=B.COLID) AS COLNAME,
	(CASE WHEN EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME=A.NAME AND XTYPE=''UQ'') THEN ''UNIQUE'' 
	WHEN EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME=A.NAME AND XTYPE=''PK'') THEN ''PRIMARY KEY''
	ELSE ''INDEX'' END)  AS UNIQ,
	(CASE WHEN A.INDID=1 THEN ''CLUSTERED'' WHEN A.INDID>1 THEN ''NONCLUSTERED'' END) AS CLUSTER
	FROM SYSINDEXES A 
	INNER JOIN SYSINDEXKEYS B ON A.INDID=B.INDID AND A.ID=B.ID
	WHERE A.ID=OBJECT_ID(''dbo.'+@TBName+''') and a.indid<>0  ) A where  a.COLNAME='''+@ColumnName+'''' 
    print(@Sql)   
    exec(@Sql)
    set @ConstraintIndex=''
    declare @strUNIQ nvarchar(50)
    declare @strInedxName nvarchar(50)
    select @strInedxName=NAME,@strUNIQ=UNIQ,@ConstraintIndex=ConstraintIndex from #tTempConstraintIndex
    if(@ConstraintIndex<>'')
    begin
		if(upper(@strUNIQ)<>'INDEX') --约束
		begin
		 set @Sql='alter table '+@TBName+' drop constraint '+@strInedxName
		end
		else
		begin --索引
		set @Sql='drop index '+@strInedxName+' on '+@TBName
		end
		print(@Sql)
		exec(@Sql)
		print(@AlterSql) 
		exec(@AlterSql)
		print(@ConstraintIndex)
		exec(@ConstraintIndex)
    end

    --------------------默认值---------------------------------------
    if object_id('tempdb..#tTempConstraintIndex') is not null drop table #tDefaultTemp
    create table #tDefaultTemp
    (
		NAME            nvarchar(200),  --约束名
		DefaultScript   nvarchar(200),  --默认值脚本
    )
	set @DefaultScript=''
	declare @strDefault nvarchar(32)
	set @Sql='
	insert into #tDefaultTemp
	SELECT O.NAME,''ALTER TABLE dbo.''+OBJECT_NAME(O.PARENT_OBJ)
	+'' ADD CONSTRAINT ''+O.NAME+'' default ''+t.text+'' for ''+C.NAME+char(13)+char(10)+char(13)+char(10)
	FROM SYSOBJECTS O 
	INNER JOIN SYSCOMMENTS T ON O.ID=T.ID
	INNER JOIN SYSCOLUMNS C ON O.PARENT_OBJ=C.ID AND C.CDEFAULT=T.ID
	WHERE O.XTYPE=''D'' AND O.PARENT_OBJ=OBJECT_ID(''dbo.'+@TBName+''') and c.name='''+@ColumnName+''''
	print(@Sql)
	exec(@Sql)
	select @strDefault=NAME,@DefaultScript=DefaultScript from #tDefaultTemp
    if(@DefaultScript<>'')
    begin
	 set @Sql='alter table '+@TBName+' drop constraint '+@strDefault
	 print(@Sql)
	 exec(@Sql)
	 print @AlterSql 
	 exec(@AlterSql)
	 print(@DefaultScript)
	 exec(@DefaultScript)
    end 
    else
	begin
	 print @AlterSql 
	 exec(@AlterSql)
	end 
set @iCount=@iCount+1	
fetch next from AlterColumn into @TBName,@ColumnName,@AlterSql 
end
print(@iCount)
close AlterColumn 
deallocate AlterColumn