您可以通过发出以下命令来实现：
SELECt FORMATMESSAGE('SELECT col = ''%s.%s.%s'' FROM %s.%s HAVINg COUNT(*) != COUNT(%s)', QUOTENAME(TABLE_SCHEMA), QUOTENAME(TABLE_NAME), QUOTENAME(COLUMN_NAME), QUOTENAME(TABLE_SCHEMA), QUOTENAME(TABLE_NAME), QUOTENAME(COLUMN_NAME) )FROM INFORMATION_SCHEMA.COLUMNSWHERe IS_NULLABLE = 'YES';db <>
fiddle演示
它将生成用于检查单个列的脚本。
HAVINg COUNT(*) != COUNT(col_name) -- it means that column contains at least single NULLHAVINg COUNT(col_name) = 0 AND COUNT(*) != 0 -- it means all values in columns are NULL
可以通过使用
STRING_AGG每个表获取单个查询来完善此方法，而使用动态SQL可以避免复制查询的需要。
编辑：
完全烘烤的解决方案：
DECLARE @sql NVARCHAr(MAX);SELECt @sql = STRING_AGG( FORMATMESSAGE('SELECT table_schema = ''%s'' ,table_name = ''%s'' ,table_col_name = ''%s'' ,row_num = COUNT(*) ,row_num_non_nulls = COUNT(%s) ,row_num_nulls = COUNT(*) - COUNT(%s) FROM %s.%s', QUOTENAME(TABLE_SCHEMA), QUOTENAME(TABLE_NAME), QUOTENAME(COLUMN_NAME), QUOTENAME(COLUMN_NAME), QUOTENAME(COLUMN_NAME), QUOTENAME(TABLE_SCHEMA), QUOTENAME(TABLE_NAME), QUOTENAME(COLUMN_NAME)), ' UNIOn ALL' + CHAr(13) ) WITHIN GROUP(ORDER BY TABLE_SCHEMA, TABLE_NAME)FROM INFORMATION_SCHEMA.COLUMNSWHERe IS_NULLABLE = 'YES' AND TABLE_NAME = ? -- filter by table name AND TABLE_SCHEMA = ?; -- filter by schema nameSELECT @sql;EXEC(@sql);db <>
fiddle演示
输出：
+---------------+-----------------+------------------+----------+--------------------+---------------+| table_schema | table_name | table_col_name | row_num | row_num_non_nulls | row_num_nulls |+---------------+-----------------+------------------+----------+--------------------+---------------+| [dbo] | [StudentScore] | [Student_Name] | 7 | 6 | 1 || [dbo] | [StudentScore] | [Student_Score] | 7 | 5 | 2 |+---------------+-----------------+------------------+----------+--------------------+---------------+
