【SQL Server】清除数据库表中的右截断字符

      这里说的右截断字符，是指类型为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;