14.2.4 非unicode字段修改为unicode字段的可行性分析.sql

sqlserver 数据库编程的绝好脚本

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 #