如何优化无游标T-SQL脚本高效统计全库列非空有效值？

直接利用系统视图生成批量统计SQL，通过集合操作一次性完成所有表列的非空值统计，彻底规避游标逐行遍历的性能损耗。以下是完整实现脚本：

DECLARE @SQL NVARCHAR(MAX);

-- 拼接所有列的统计语句
SELECT @SQL = STRING_AGG(
    CONCAT(
        'SELECT ''', 
        QUOTENAME(s.name), '.', QUOTENAME(t.name), ''' AS TableName, ',
        '''', QUOTENAME(c.name), ''' AS ColumnName, ',
        'COUNT(CASE ',
        -- 针对日期类型列，排除'1900-01-01'
        CASE WHEN ty.name IN ('datetime', 'datetime2', 'smalldatetime', 'date') 
             THEN 'WHEN ', QUOTENAME(c.name), ' <> ''1900-01-01'' THEN ', QUOTENAME(c.name)
             ELSE 'WHEN ', QUOTENAME(c.name), ' IS NOT NULL THEN ', QUOTENAME(c.name)
        END,
        ' END) AS NonNullCount ',
        'FROM ', QUOTENAME(s.name), '.', QUOTENAME(t.name)
    ),
    ' UNION ALL '
)
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN sys.types ty ON c.system_type_id = ty.system_type_id
-- 排除系统表（可选，根据需求调整）
WHERE t.is_ms_shipped = 0;

-- 执行生成的统计SQL
EXEC sp_executesql @SQL;

关键说明 #

• 系统视图关联：通过sys.tables、sys.columns、sys.schemas、sys.types获取数据库所有用户表的列信息，无需游标遍历。
• 条件分支处理：根据列的数据类型动态生成统计逻辑——日期类型额外排除'1900-01-01'，其他类型仅统计非空值。
• 批量合并执行：用STRING_AGG（SQL Server 2017+支持）将所有列的统计语句通过UNION ALL合并为单个SQL，一次性执行，大幅减少多次执行的开销。

兼容与优化建议 #

1. 如果是SQL Server 2016及更早版本，替换STRING_AGG为FOR XML PATH的拼接方式：

   SELECT @SQL = STUFF((
       SELECT ' UNION ALL ' + CONCAT(
           'SELECT ''', QUOTENAME(s.name), '.', QUOTENAME(t.name), ''' AS TableName, ',
           '''', QUOTENAME(c.name), ''' AS ColumnName, ',
           'COUNT(CASE ',
           CASE WHEN ty.name IN ('datetime', 'datetime2', 'smalldatetime', 'date') 
                THEN 'WHEN ', QUOTENAME(c.name), ' <> ''1900-01-01'' THEN ', QUOTENAME(c.name)
                ELSE 'WHEN ', QUOTENAME(c.name), ' IS NOT NULL THEN ', QUOTENAME(c.name)
           END,
           ' END) AS NonNullCount ',
           'FROM ', QUOTENAME(s.name), '.', QUOTENAME(t.name)
       )
       FROM sys.tables t
       JOIN sys.columns c ON t.object_id = c.object_id
       JOIN sys.schemas s ON t.schema_id = s.schema_id
       JOIN sys.types ty ON c.system_type_id = ty.system_type_id
       WHERE t.is_ms_shipped = 0
       FOR XML PATH(''), TYPE
   ).value('.', 'NVARCHAR(MAX)'), 1, 11, '');
   

2. 若数据库包含超大表，可考虑在FROM子句后添加WITH (NOLOCK)（需注意脏读风险，仅适用于允许近似统计的场景）。
3. 可通过添加WHERE条件过滤特定 schema 或表，缩小统计范围进一步提升性能。

内容的提问来源于stack exchange，提问作者Senthil P Nathan