如何查询数据库表中各列的非空记录数及对应列名？

要统计目标表中每个字段的非空记录数，你需要生成动态SQL来逐个字段计算COUNT(列名)（COUNT(列名)会自动忽略NULL值，正好匹配你要的"有效记录数"需求）。以下是主流数据库的实现方式：

MySQL/MariaDB #

利用INFORMATION_SCHEMA.COLUMNS拼接动态SQL后执行：

SET @table_name = 'Table_Name';
SET @sql = NULL;

SELECT GROUP_CONCAT(
    'SELECT ''', COLUMN_NAME, ''' AS `Column Name`, COUNT(', COLUMN_NAME, ') AS `Count` FROM ', @table_name
    SEPARATOR ' UNION ALL '
) INTO @sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
AND TABLE_SCHEMA = DATABASE(); -- 指定当前数据库，避免跨库冲突

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQL Server #

通过拼接动态SQL并执行：

DECLARE @table_name NVARCHAR(128) = 'Table_Name';
DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql += 'SELECT ''' + COLUMN_NAME + ''' AS [Column Name], COUNT(' + COLUMN_NAME + ') AS [Count] FROM ' + @table_name + ' UNION ALL '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
AND TABLE_SCHEMA = SCHEMA_NAME(); -- 指定当前架构

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

EXEC sp_executesql @sql;

说明 #

动态SQL会为每个列生成单独的统计语句，再用UNION ALL合并结果，最终输出你需要的Column Name | Count格式。

内容的提问来源于stack exchange，提问作者Marvin Raymund Popioco