这里说的右截断字符,是指类型为char或varchar的字段中,最后一个双字节字符(如汉字)由于字段宽度不够被切断为只剩下1个字节的字符,这个字符会被显示为乱码。SQL Server 2000及更早的版本,碰到要保存的字符串长度大于字段长度时,会把字符串截成字段的长度再保存,这个过程会出现右截断字符。数据库升级到新版本后,这些右截断字符仍然存在,会妨碍数据导入/导出、BCP in/out等操作,因此需要把它们清除掉。
清除右截断字符的sql脚本如下所示。运行该脚本后生会产生清除右截断字符的SQL命令,而不是直接进行清除右截断字符的操作。在正式执行这些SQL命令前,可以先评估核实一下。
DECLARE @dbname VARCHAR(128) = 'TargetDB';
IF NOT EXISTS (SELECT * FROM sys.databases WHERE NAME=@dbname)
BEGIN
PRINT 'Error: Database ''' + @dbname + ''' Not Exists.';
RETURN;
END;
SET NOCOUNT ON;
DECLARE @tabname VARCHAR(128), @colname VARCHAR(128), @sql NVARCHAR(4000);
DECLARE @count INT;
IF OBJECT_ID('tempdb..#tmp', 'U') IS NOT NULL
DROP TABLE #tmp;
CREATE TABLE #tmp (tabname VARCHAR(256), colname VARCHAR(256));
SET @sql = 'insert into #tmp ';
SET @sql = @sql + 'SELECT tabname=b.name, colname=a.name'
SET @sql = @sql + ' FROM ' + @dbname + '.sys.columns a, '+ @dbname + '.sys.tables b'
SET @sql = @sql + ' WHERE a.object_id = b.object_id AND b.is_ms_shipped=0'
SET @sql = @sql + ' AND a.system_type_id IN (SELECT system_type_id FROM '+ @dbname+ '.sys.types WHERE name IN (''char'', ''varchar''))';
--PRINT @sql;
EXEC (@sql);
CREATE CLUSTERED INDEX index_tmp ON #tmp (tabname, colname);
SET @tabname = (SELECT MIN(tabname) FROM #tmp);
WHILE @tabname IS NOT NULL
BEGIN
SET @colname = (SELECT MIN(colname) FROM #tmp WHERE tabname=@tabname);
WHILE @colname IS NOT NULL
BEGIN
SET @sql = 'SELECT @count=COUNT(*) FROM ' + @dbname + '.dbo.' + @tabname + ' ';
SET @sql = @sql + ' WHERE ' + @colname + ' IS NOT NULL';
SET @sql = @sql + ' AND ' + @colname + ' <> ''''';
SET @sql = @sql + ' AND RIGHT(' + @colname + ', 1) <> '' ''';
SET @sql = @sql + ' AND CAST(CAST(RIGHT(' + @colname + ', 1) AS VARBINARY) AS VARCHAR) = ''''';
--PRINT @sql;
EXEC sp_executesql @sql, N'@count int output', @count OUTPUT;
IF @count > 0
BEGIN
SET @sql = 'UPDATE ' + @dbname +'.dbo.' + @tabname + ' SET ';
SET @sql = @sql + @colname + '= RTRIM(CAST(CAST(' + @colname + ' AS VARBINARY(8000)) AS VARCHAR(8000)))';
SET @sql = @sql + ' WHERE ' + @colname + ' IS NOT NULL';
SET @sql = @sql + ' AND ' + @colname + ' <> ''''';
SET @sql = @sql + ' AND RIGHT(' + @colname + ', 1) <> '' ''';
SET @sql = @sql + ' AND CAST(CAST(RIGHT(' + @colname + ', 1) AS VARBINARY) AS VARCHAR) = ''''';
PRINT @sql;
END;
SET @colname = (SELECT MIN(colname) FROM #tmp WHERE tabname=@tabname AND colname>@colname);
END;
SET @tabname = (SELECT MIN(tabname) FROM #tmp WHERE tabname>@tabname);
END;
SET NOCOUNT OFF;