查找單個表的 500 列中的空值計數

我在 SQL Server 的一個表中有 500 列。我想查找每列中空值的計數。有沒有辦法使用游標和動態 SQL 來做到這一點？

不使用游標的替代方法：

-- Set the target table details here
DECLARE 
   @Schema sysname = N'Production',
   @Table sysname = N'Product',
   @SQL nvarchar(max) = N'SELECT ';

DECLARE
   @QName sysname = QUOTENAME(@Schema) + N'.' + QUOTENAME(@Table);

SET @SQL +=
(
   SELECT
       NCHAR(13) + NCHAR(9) +
       QUOTENAME(C.name + N'_NullCount') + ' = ' +
       N'COUNT_BIG(*) - COUNT_BIG(' +
       QUOTENAME(C.name) + N'),'
   FROM sys.columns AS C
   WHERE 
       C.[object_id] = OBJECT_ID(@QName)
       AND C.is_nullable = CONVERT(bit, 1)
       AND C.is_filestream = CONVERT(bit, 0)
       AND C.is_column_set = CONVERT(bit, 0)
   ORDER BY 
       C.column_id
   FOR XML PATH (''), TYPE
).value('.[1]/text()[1]', 'nvarchar(max)');

SET @SQL = 
   STUFF(@SQL, LEN(@SQL), 1,  NCHAR(13) + N'FROM ' + @QName + N';');

PRINT @SQL;

EXECUTE (@SQL);

給出的 AdventureWorks 範例中生成的 SQL 是：

SELECT 
   [Color_NullCount] = COUNT_BIG(*) - COUNT_BIG([Color]),
   [Size_NullCount] = COUNT_BIG(*) - COUNT_BIG([Size]),
   [SizeUnitMeasureCode_NullCount] = COUNT_BIG(*) - COUNT_BIG([SizeUnitMeasureCode]),
   [WeightUnitMeasureCode_NullCount] = COUNT_BIG(*) - COUNT_BIG([WeightUnitMeasureCode]),
   [Weight_NullCount] = COUNT_BIG(*) - COUNT_BIG([Weight]),
   [ProductLine_NullCount] = COUNT_BIG(*) - COUNT_BIG([ProductLine]),
   [Class_NullCount] = COUNT_BIG(*) - COUNT_BIG([Class]),
   [Style_NullCount] = COUNT_BIG(*) - COUNT_BIG([Style]),
   [ProductSubcategoryID_NullCount] = COUNT_BIG(*) - COUNT_BIG([ProductSubcategoryID]),
   [ProductModelID_NullCount] = COUNT_BIG(*) - COUNT_BIG([ProductModelID]),
   [SellEndDate_NullCount] = COUNT_BIG(*) - COUNT_BIG([SellEndDate]),
   [DiscontinuedDate_NullCount] = COUNT_BIG(*) - COUNT_BIG([DiscontinuedDate])
FROM [Production].[Product];

執行計劃：

結果（樣本）：

是的。但是，如果它是一次性操作，您可以通過將“列”標籤從 SSMS 中的對象資源管理器拖到查詢視窗中來對其進行硬編碼，這會將列列表填充到您的查詢中。如果絕對沒有其他選擇，您只會走動態路​​線（並且在架構更改時不願意進行更新不是正當理由！）

Declare @Sql Nvarchar(Max) = 'Select '
Declare @Schema Sysname, @Table Sysname, @Column Sysname

Declare DontDoThis Cursor Local Forward_Only Read_Only Static For
Select  s.name,
       t.name,
       c.name
From    sys.schemas s
Join    sys.tables t
On      s.schema_id = t.schema_id
Join    sys.columns c
On      t.object_id = c.object_id
Where   s.name = 'smo'
And     t.name = 'Server'
Order By s.name, t.name, c.column_id

Open DontDoThis
Fetch Next From DontDoThis Into @Schema, @Table, @Column
While @@Fetch_Status = 0
Begin
   Print   @Column

   If      @Sql <> 'Select '
           Set     @Sql = @Sql + ', '
   Set     @Sql = @Sql + 'Sum(Case When ' + Quotename(@Column) + ' Is Null Then 1 Else 0 End) As ' + Quotename(@Column + 'Count')

   Fetch Next From DontDoThis Into @Schema, @Table, @Column
End

Close DontDoThis
Deallocate DontDoThis

Set     @Sql = @Sql + ' From ' + Quotename(@Schema) + '.' + Quotename(@Table)

Exec    sp_executesql @Sql

引用自：https://dba.stackexchange.com/questions/135729