如何把nvarchar转换成varchar类型？

[duibudui]

大家帮忙啦。

[liangCK]

14.3.2 将所有的char、varchar改为nchar、nvarchar.sql

CREATE PROC p_ToUnicode
@type tinyint=0  --修改方式，0=仅查询可修改情况,1=仅所有列可修改时才修改,2=修改可修改列，报告不可修改列
AS
SET NOCOUNT ON
--查询非unicode列转换为unicode列的可行性
SELECT TableName=o.name,FieldName=c.name,
	FieldType=t.name+N'('+CAST(c.prec as varchar)+N')'
		+CASE WHEN c.isnullable=1 THEN N'' ELSE N' NOT' END
		+N' NULL',
	NoChangeCause=CAST(STUFF(
		CASE WHEN COLUMNPROPERTY(c.id,c.name,N'IsComputed')=1
			THEN N',计算列' ELSE N'' END
		+CASE WHEN c.cdefault=0 THEN N'' ELSE N',列具有默认值' END
		+CASE WHEN EXISTS(
				SELECT * FROM sysindexkeys idxk,sysindexes idx
				WHERE idxk.id=c.id 
					AND idxk.colid=c.colid
					AND idxk.id=idx.id
					AND idxk.indid=idx.indid
					AND idx.indid NOT IN(0,255)
					AND INDEXPROPERTY(idx.id,idx.name,N'IsAutoStatistics')=0)
			THEN N',列被主键、唯一键、索引、STATISTICS引用' ELSE N'' END
		+CASE WHEN EXISTS(
				SELECT * FROM sysforeignkeys WHERE fkeyid=c.id AND fkey=c.colid)
			THEN N',列被外键约束引用' ELSE N'' END
		+CASE WHEN EXISTS(
				SELECT * FROM sysobjects oc,sysdepends d
				WHERE oc.parent_obj=o.id
					AND OBJECTPROPERTY(oc.id,N'IsCheckCnst')=1
					AND d.id=oc.id
					AND d.depnumber=c.colid)
			THEN N',列被CHECK约束引用' ELSE N'' END,1,1,N'') as nvarchar(4000))
INTO # FROM sysobjects o,syscolumns c,systypes t
WHERE o.id=c.id and o.status>=0
	AND OBJECTPROPERTY(o.id,N'IsUserTable')=1
	AND t.xusertype=c.xusertype
	AND t.name in('char','varchar')
IF @@ROWCOUNT=0 RETURN

--如果需要，修改非unicode列为unicode列
IF @type=2 OR NOT EXISTS(SELECT * FROM # WHERE NoChangeCause>'')
BEGIN
	SET XACT_ABORT ON
	BEGIN TRAN
		DECLARE tb CURSOR LOCAL
		FOR
		SELECT N'ALTER TABLE '+QUOTENAME(TableName)
			+N' ALTER COLUMN '+QUOTENAME(FieldName)
			+N' N'+FieldType
		FROM #
		WHERE NoChangeCause IS NULL
		DECLARE @sql nvarchar(4000)
		OPEN tb
		FETCH tb INTO @sql
		WHILE @@FETCH_STATUS=0
		BEGIN
			EXEC sp_executesql @sql
			FETCH tb INTO @sql
		END
		CLOSE tb
		DEALLOCATE tb
	COMMIT TRAN
END

--显示不能修改的列
SELECT TableName,FieldName,FieldType,
	NoChangeCause=ISNULL(NoChangeCause,N'可以修改(或者已经修改成功)')
FROM #
ORDER BY CASE WHEN NoChangeCause IS NULL THEN 1 ELSE 0 END,TableName

[duibudui]

我要的是从nvarchar到varchar，楼上的是varchar到nvarchar吧。

[ojuju10]

用游标去转换！

[zhu_gx]

帮你顶

[laowan688]

定

[yesyesyes]

cast
or
convert

[ojuju10]

declare @col varchar(100),@len int,@tab varchar(100)

declare roy cursor for 
select a.name,a.length,c.name from syscolumns a,systypes b,sysobjects c
where a.xtype=b.xtype and a.id=c.id and c.xtype='u' and b.name='nvarchar'

open roy
fetch next from roy into @col,@len,@tab
while @@fetch_status=0
begin
declare @sql varchar(1000)
set @sql='Alter table '+@tab+' alter column '+@col+' varchar('+ltrim(@len*2)+')'
--print @sql 先print再执行
Exec(@sql)
fetch next from roy into @col,@len,@tab
end
close roy
deallocate roy

[jackyqk]

弱弱的问下，转换的目的是什么

[duibudui]

有什么函数可以用么？ 我需要把这个在列转换里，谁能给个cast or convert的例子，我这里无法去查

[duibudui]

up