SQL Server中列名存于变量时LEN()函数无法正确计算列值长度

直接指定列名调用LEN()可正确获取列内最长值的长度，但通过变量传递列名时，返回的是列名字符串的长度。例如：

• 列定义：enrolledTerm varchar(5)、studentSSN varchar(9)、fullName varchar(500)（该列最长值为14字符）
• 直接调用：LEN(enrolledTerm)返回5，LEN(fullName)返回14；通过变量传递列名时返回14、10、8（对应列名本身的长度）

当前使用游标遍历列名的代码无法实现需求：

DECLARE @fieldName VARCHAR(MAX)

DECLARE cursorField CURSOR FOR 
    SELECT name 
    FROM sys.columns 
    WHERE object_id = OBJECT_ID('dbo.enrollment');      

OPEN cursorField;

FETCH NEXT FROM cursorField INTO @fieldName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT TOP 1 LEN(@fieldName) AS fieldLen 
    FROM dbo.enrollment;

    FETCH NEXT FROM cursorField INTO @fieldName;
END;

CLOSE cursorField;
DEALLOCATE cursorField;

背景：大型归档项目，需将数百个表的varchar(500)列调整为对应列最长值的长度，以优化空间使用。

SQL Server会将变量视为字符串而非列名，必须通过动态SQL将变量内容拼接为可执行的SQL语句。

方案1：游标+动态SQL（逐列计算） #

修改原有游标逻辑，拼接动态SQL执行：

DECLARE @fieldName VARCHAR(MAX)
DECLARE @sql NVARCHAR(MAX)

DECLARE cursorField CURSOR FOR 
    SELECT name 
    FROM sys.columns 
    WHERE object_id = OBJECT_ID('dbo.enrollment')
    AND system_type_id = 167; -- 仅筛选varchar类型列

OPEN cursorField;

FETCH NEXT FROM cursorField INTO @fieldName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 用QUOTENAME处理列名，避免特殊字符或关键字导致语法错误
    SET @sql = N'
        SELECT ''' + @fieldName + ''' AS columnName, MAX(LEN(' + QUOTENAME(@fieldName) + ')) AS maxLength
        FROM dbo.enrollment
    ';
    EXEC sp_executesql @sql;

    FETCH NEXT FROM cursorField INTO @fieldName;
END;

CLOSE cursorField;
DEALLOCATE cursorField;

方案2：无游标批量计算（更高效） #

无需游标，一次性生成所有列的最长长度：

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
    SELECT ''' + name + ''' AS columnName, MAX(LEN(' + QUOTENAME(name) + ')) AS maxLength FROM dbo.enrollment
    UNION ALL
'
FROM sys.columns 
WHERE object_id = OBJECT_ID('dbo.enrollment')
AND system_type_id = 167;

-- 移除末尾多余的UNION ALL
SET @sql = LEFT(@sql, LEN(@sql) - 10);

EXEC sp_executesql @sql;

进阶：自动生成ALTER TABLE语句 #

如果需要直接调整列长度，可生成修改列的SQL语句：

DECLARE @alterSql NVARCHAR(MAX) = N'';

SELECT @alterSql += N'
    ALTER TABLE dbo.enrollment ALTER COLUMN ' + QUOTENAME(name) + ' VARCHAR(' + CAST(MAX(LEN(' + QUOTENAME(name) + ')) AS VARCHAR(10)) + ');
'
FROM sys.columns 
WHERE object_id = OBJECT_ID('dbo.enrollment')
AND system_type_id = 167;

-- 先打印预览确认，无误后取消注释执行
PRINT @alterSql;
-- EXEC sp_executesql @alterSql;

内容的提问来源于stack exchange，提问作者BillsBayou