sp_MSforeachdb中含growth判断的SQL查询报错问题排查

单独执行以下代码可正常运行：

SELECT          
    CASE 
        WHEN a.type_desc = 'ROWS' 
            THEN
                CASE 
                    WHEN max_size / 128.0 < 0 and growth > 0 
                        THEN 0
                    WHEN growth = 0 
                        THEN CAST(size / 128.0 AS DECIMAL(15, 2))
                    ELSE CAST(max_size / 128.0 AS DECIMAL(15, 2))
                END
    END AS row_maxsize
FROM          
    sys.database_files a
LEFT JOIN 
    sys.filegroups fg ON a.data_space_id = fg.data_space_id

但将其嵌入调用sp_MSforeachdb的批量查询时，必须移除WHEN growth = 0 THEN CAST(size / 128.0 AS DECIMAL(15, 2))这一行才能正常执行，否则会返回大量类似以下的报错：

Msg 207, Level 16, State 1, Line 37

Invalid column name 'ALLOCATED_LOG_SIZE_MB'.

完整批量查询代码如下：

--SEMPRE RETORNA 0% SE O DATABASE TIVER UM MDF/NDF COM MAXSIZE UNLIMITED
BEGIN
    SET NOCOUNT ON

    DECLARE @temp_dbspace_solaire TABLE (
        DATABASE_NAME VARCHAR(100),
        ALLOCATED_LOG_SIZE_MB FLOAT,
        ALLOCATED_ROW_SIZE_MB FLOAT,
        ALLOCATED_TOTAL_SIZE_MB FLOAT,
        USED_ROW_SPACE_MB FLOAT,
        MAX_ROW_SIZE FLOAT,
        MAX_ROW_UTILIZATION DECIMAL(15, 2),
        MAX_LOG_UTILIZATION DECIMAL(15, 2)
    );

    INSERT INTO @temp_dbspace_solaire (database_name, allocated_log_size_mb, allocated_row_size_mb, allocated_total_size_mb,USED_ROW_SPACE_MB,MAX_ROW_SIZE,MAX_ROW_UTILIZATION,MAX_LOG_UTILIZATION)
    EXEC sp_MSforeachdb '
    USE [?];
    WITH CTE AS (
        SELECT
            ALLOCATED_LOG_SIZE_MB = CAST(CASE WHEN a.type_desc = ''LOG'' THEN size END * 8. / 1024 AS DECIMAL(12, 2)),
            ALLOCATED_ROW_SIZE_MB = CAST(CASE WHEN a.type_desc = ''ROWS'' THEN size END * 8. / 1024 AS DECIMAL(12, 2)),
            ALLOCATED_TOTAL_SIZE_MB = CAST(size * 8. / 1024 AS DECIMAL(12, 2)),
            USED_ROW_SPACE_MB = CAST(CASE WHEN a.type_desc = ''ROWS'' THEN SIZE/128.0 END - ((SIZE/128.0) - CAST(FILEPROPERTY(a.name, ''SPACEUSED'') AS INT)/128.0) AS DECIMAL(15, 2)),
            ROW_SPACEUSED = CAST(CASE WHEN a.type_desc = ''ROWS'' THEN fileproperty(a.name, ''spaceused'') END / 128.0 AS DECIMAL(15, 2)),
            LOG_SPACEUSED = CAST(CASE WHEN a.type_desc = ''LOG'' THEN fileproperty(a.name, ''spaceused'') END / 128.0 AS DECIMAL(15, 2)),
            CASE WHEN a.type_desc = ''ROWS'' THEN
                CASE 
                    WHEN growth = 0 THEN CAST(size / 128.0 AS DECIMAL(15, 2))
                    WHEN max_size / 128.0 < 0 and growth > 0 THEN 0
                    ELSE CAST(max_size / 128.0 AS DECIMAL(15, 2))
                END
                END AS row_maxsize,
                
            CASE WHEN a.type_desc = ''ROWS'' THEN
                CASE
                    WHEN max_size = -1 and growth > 0 THEN 1
                    ELSE 0
                END
                END AS hasNegativeMaxSize,
                
                CASE 
                WHEN a.type_desc = ''LOG'' THEN CAST(max_size / 128.0 AS DECIMAL(15, 2))
                ELSE 0.0
            END as max_logsize


        FROM sys.database_files a
        LEFT JOIN sys.filegroups fg ON a.data_space_id = fg.data_space_id
    )
    SELECT
        DB_NAME() AS database_name,
        SUM(ALLOCATED_LOG_SIZE_MB),
        SUM(ALLOCATED_ROW_SIZE_MB),
        SUM(ALLOCATED_TOTAL_SIZE_MB),
        SUM(USED_ROW_SPACE_MB),
        CASE
            WHEN MAX(hasNegativeMaxSize) = 1 THEN 0
            ELSE NULLIF(SUM(row_maxsize), 0)
        END AS MAX_ROW_SIZE,
        CASE
            WHEN MAX(hasNegativeMaxSize) = 1 THEN 0
            ELSE SUM(ROW_SPACEUSED) / NULLIF(SUM(row_maxsize), 0) * 100
        END AS MAX_ROW_UTILIZATION,
        SUM(LOG_SPACEUSED) / NULLIF(SUM(max_logsize), 0) * 100
    FROM CTE;
    ';

    SELECT * FROM @temp_dbspace_solaire;
END;

核心原因是sp_MSforeachdb存储过程的参数长度限制：

• sp_MSforeachdb内部用于接收执行命令的@command参数类型为varchar(8000)，传入的动态SQL代码长度不能超过8000字符。
• 保留WHEN growth = 0 THEN CAST(size / 128.0 AS DECIMAL(15, 2))这一行时，整个动态SQL的总长度超出了8000字符上限，导致代码被自动截断。截断后的SQL会出现语法不完整的情况（比如CTE定义被打断，部分列未正确声明），后续SELECT语句引用这些未定义的列时，就会抛出"列名无效"的报错。
• 移除这一行后，动态SQL总长度缩短到8000字符以内，代码能完整执行，因此不再触发报错。

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