SQL Server 批次取得 TEXT/NTEXT 欄位實際資料長度

背景

TEXT/NTEXT 已棄用 (建議改用 VARCHAR(MAX)/NVARCHAR(MAX))，但舊系統仍大量存在。規劃轉型前需評估各欄位最大長度，以決定目標型別長度。

單次查詢取得多欄位最大長度

SELECT 
    MAX(DATALENGTH(PayBeizhu))  AS PayBeizhu_MaxBytes,
    MAX(DATALENGTH(Flighty2))   AS Flighty2_MaxBytes,
    MAX(DATALENGTH(Flighty1))   AS Flighty1_MaxBytes,
    MAX(DATALENGTH(Remarks))    AS Remarks_MaxBytes,
    MAX(DATALENGTH(RInfo))      AS RInfo_MaxBytes,
    MAX(DATALENGTH(RLog))       AS RLog_MaxBytes
FROM dbo.Orders WITH (NOLOCK);

關鍵函數說明

• DATALENGTH()：回傳儲存位元組數，TEXT/NTEXT/VARCHAR(MAX) 皆適用
• LEN()：回傳字元數，**不支援 TEXT/NTEXT**，會報錯
• NOLOCK：避免鎖定衝突，接受髒讀風險 (僅統計長度通常可接受)

進階：一次查全表所有 LOB 欄位

DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += 
    'MAX(DATALENGTH(' + QUOTENAME(c.name) + ')) AS ' + QUOTENAME(c.name + '_MaxBytes') + ','
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID('dbo.Orders')
  AND t.name IN ('text','ntext','varchar','nvarchar','varbinary')
  AND c.max_length = -1;  -- MAX 型別

SET @sql = 'SELECT ' + LEFT(@sql, LEN(@sql)-1) + ' FROM dbo.Orders WITH (NOLOCK);';
EXEC sp_executesql @sql;

轉型建議對照表

轉型腳本範例

ALTER TABLE dbo.Orders ALTER COLUMN PayBeizhu VARCHAR(MAX) NULL;
-- 執行後建議 REBUILD 索引、UPDATE STATISTICS