非UNICODE字段修改为UNICODE字段的可行性分析

CREATE PROC p_CheckCntUNICODE
@flag bit=0  --1=转换为UNICODE后,字段长度/2,<>1转换时使用旧长度,只查询定义长度超过4000,而且有记录超过此长度的表+列
AS
SET NOCOUNT ON
--定义保存查询结果的表
CREATE TABLE #(TableName sysname,FieldName sysname,Type sysname,SQL nvarchar(4000))

--查询处理
DECLARE tb CURSOR LOCAL
FOR
SELECT N'SELECT '+QUOTENAME(c.name)
 +N' FROM '+QUOTENAME(o.name)
 +N' WHERE DATALENGTH(RTRIM('+QUOTENAME(c.name)
 +N'))>'+CASE WHEN @flag=0 THEN N'4000' ELSE RTRIM(c.prec/2) END,
 N'INSERT # VALUES(N'+QUOTENAME(o.name,N'''')
 +N',N'+QUOTENAME(c.name,N'''')
 +N',N'+QUOTENAME(t.name+N'('+CAST(c.prec as varchar)+N')',N'''')
 +N',@sql)'
FROM sysobjects o,syscolumns c,systypes t
WHERE o.id=c.id
 AND c.xusertype=t.xusertype
 AND OBJECTPROPERTY(o.id,N'IsUserTable')=1
 AND t.name IN(N'char',N'varchar')
 AND c.prec>CASE WHEN @flag=0 THEN 4000 ELSE 0 END
ORDER BY o.id,c.colid

DECLARE @sql nvarchar(4000),@sql1 nvarchar(4000)
OPEN tb
FETCH tb INTO @sql,@sql1
WHILE @@FETCH_STATUS=0
BEGIN
 SET @sql1=N'IF EXISTS('+@sql+N') '+@sql1
 EXEC sp_executesql @sql1,N'@sql nvarchar(4000)',@sql
 FETCH tb INTO @sql,@sql1
END
CLOSE TB
DEALLOCATE TB
SELECT * FROM #

转载于:https://www.cnblogs.com/dushu/archive/2012/06/05/2537213.html