如何能将表里面的字段批量改成NVARCHAR？
各位论坛的朋友们，小弟的SQL数据库里面大概有2百多张表，每张表都有很多VARCHAR的字段，如何做，才能将数据库里面的所有表，字段类型为VARCHAR的，全部一次性改为NVARCHAR呢？另外，原来的长度是多少，改成NVARCHAR之后也要设置为多少，例如：原来的类型是 VARCHAR 30,转换之后的类型为：NVARCHAR 30,请问这样可以实现吗？因为手工来一个一个的改，实在是太累人的，诚心向各位朋友求教，谢谢！
[解决办法]
- SQL code
-- xtype = '167' 字段型 varchar 可用 select * from systypes 查看Declare curAlterInfo Cursor For Select a.Name AlterFieldName, a.Length, a.IsNullable, b.Name AlterTableName From SysColumns a, SysObjects b Where a.id = b.id and a.xType = '167' and b.xType = 'u' and a.cdefault = 0declare @AlterFieldName nvarchar(50), -- 修改的字段 @AlterTableName nvarchar(50), -- 修改的表名 @Length int, -- 字段以度 @IsNullable bit, -- 字段是否允空 @PkName nvarchar(50), -- 主名 @PkFieldName nvarchar(500), --主字段名 @TmpFieldName nvarchar(50), @TmpTableName nvarchar(50), @Sql nvarchar(500)Set NoCount OnBegin Tranopen curAlterInfoFetch curAlterInfo Into @AlterFieldName, @Length, @IsNullable, @AlterTableNameWhile @@Fetch_Status=0Begin print @AlterTableName --查修改的表是否有主 If Exists(Select Name From SysObjects Where xType = 'PK' and Parent_Obj = (Select id From SysObjects Where Name = @AlterTableName)) Begin Set @TmpTableName = @AlterTableName -- 取得主名 Select @PkName = Name From SysObjects Where xType = 'PK' and Parent_Obj = (Select id From SysObjects Where Name = @AlterTableName) Set @PkFieldName = '' -- 主字段 Declare curPkFieldName Cursor For Select b.Name From SysIndexKeys a, SysColumns b Where a.id = (Select id From SysIndexes Where Name = @PkName) and a.indid = 1 and a.colid = b.colid and a.id = b.id -- 取得所有的主字段 Open curPkFieldName Fetch curPkFieldName Into @TmpFieldName While @@fetch_status = 0 Begin Set @PkFieldName = @PkFieldName + @TmpFieldName + ',' Fetch curPkFieldName Into @TmpFieldName End Close curPkFieldName Deallocate curPkFieldName -- 除主 Set @Sql = 'ALTER TABLE '+ @AlterTableName + ' DROP CONSTRAINT ' + @PkName Print @Sql Exec(@Sql) end -- 修改字段 Set @Sql = 'ALTER TABLE ' + @AlterTableName + ' ALTER COLUMN ' + @AlterFieldName + ' NVARCHAR( ' + CAST(@Length AS NVARCHAR) + ')' -- 是否允空 if @IsNullable = 0 Set @Sql = @Sql + ' NOT NULL' Print @sql Exec(@sql) Fetch curAlterInfo Into @AlterFieldName, @Length, @IsNullable, @AlterTableName -- 建主 If (@AlterTableName <> @TmpTableName or @@fetch_status <> 0) and @PkFieldName <> '' Begin Set @PkFieldName = Left(@PkFieldName, Len(@PkFieldName) - 1) Set @Sql = ' ALTER TABLE ' + @TmpTableName + ' ADD CONSTRAINT ' + @PkName + ' PRIMARY KEY CLUSTERED(' + @PkFieldName + ') ON [PRIMARY]' Print @Sql Exec(@Sql) print '-----------------------------' Set @PkFieldName = '' EndEndClose curAlterInfoDeallocate curAlterInfoIf @@Error > 0 Rollback TranElse Commit TranSet NoCount Off
[解决办法]
- SQL code
有什么哪里不明白的指出来~~
[解决办法]
------解决方案--------------------
- SQL code
EXEC sp_MSforeachtable 'declare @sql varchar(max);set @sql=''''select @sql=@sql+'' alter table ? alter column '' + name+'' nvarchar(''+ltrim(length)+'');'' from syscolumns where id=object_id(''?'') and xtype=167;exec(@sql)'go
[解决办法]
[解决办法]
[解决办法]
- SQL code
--存储过程中的varchar变量换成nvarcharif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_convert]') and xtype in (N'FN', N'IF', N'TF'))drop function [dbo].[f_convert]GO--创建一个函数,替换指定字符串中的varchar为nvarcharcreate function f_convert(@text nvarchar(4000))returns varchar(8000)asbegin declare @i1 int,@i2 int,@i3 varchar(20),@re varchar(8000) select @i1=patindex('% varchar(%)%',@text) ,@i2=charindex(')',@text,@i1) ,@i3=substring(@text,@i1+9,@i2-@i1-9) ,@re='' while @i1>0 and @i2>0 begin if isnumeric(@i3)=1 if @i3>4000 select @re=@re+left(@text,@i1)+'nvarchar(4000)' else select @re=@re+left(@text,@i1)+'nvarchar(' +substring(@text,@i1+9,@i2-@i1-8) else select @re=@re+left(@text,@i2) select @text=stuff(@text,1,@i2,'') ,@i1=patindex('% varchar(%)%',@text) ,@i2=charindex(')',@text,@i1) ,@i3=substring(@text,@i1+9,@i2-@i1-9) end return(@re+@text) endgo--调用函数实现转换处理:declare @id int,@name sysnamedeclare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000)declare tb cursor local for select top 1 id,'['+replace(name,']',']]')+']' from sysobjects awhere xtype='P' and status>=0 and exists( select 1 from syscomments where id=a.id and charindex('varchar(',text)>0)open tbfetch tb into @id,@namewhile @@fetch_status=0begin select @s1='',@s2='',@s3='' select @s1=@s1+',@'+cast(colid as varchar)+' varchar(8000)' ,@s2=@s2+';select @'+cast(colid as varchar) +'=f_convert(text) from syscomments where id=' +cast(@id as varchar)+' and colid='+cast(colid as varchar) ,@s3=@s3+'+@'+cast(colid as varchar) from syscomments where id=@id order by colid select @s1=stuff(@s1,1,1,'') ,@s2=stuff(@s2,1,1,'') ,@s3=stuff(@s3,1,1,'') exec('declare '+@s1+' '+@s2+' drop proc '+@name+' exec('+@s3+') ') fetch tb into @id,@nameendclose tbdeallocate tb