Finding count of null values in 500 columns of a single table

Question

I have 500 columns in a table in SQL Server. I want to find the count of null values in each column. Is there a way to do it using a cursor and dynamic SQL?

Answer 1

An alternative that does not use a cursor:

-- 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);

The SQL generated on the AdventureWorks example given is:

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];

Execution plan:

Results (sample):

Answer 2

Yes. But if it's a one-time operation, you can instead hardcode it by dragging the "Columns" label from the Object Explorer in SSMS into the Query window, which will populate the list of columns into your query. You'd only go the dynamic route if there was absolutely no other option (and unwillingness to do updates when the schema changes isn't a valid reason!)

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

Answer 3

Building on Paul White's example, to avoid worrying about the following warning without doing a SET ANSI_WARNINGS OFF:

Warning: Null value is eliminated by an aggregate or other SET operation.

You can do it with SUM and CASE like this:

--Set the target table details here
DECLARE
    @Schema sysname = 'dbo',
    @Table  sysname = 'TableName'

DECLARE
    @SQL nvarchar(max) = '',
    @QName sysname = QUOTENAME(@Schema) + '.' + QUOTENAME(@Table)

SELECT
    @SQL += ',
    SUM(CASE WHEN ' + QUOTENAME(C.name) + N' IS NULL THEN 1 ELSE 0 END) AS ' + QUOTENAME(C.name + '_NullCount')
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)

SET @SQL = 'SELECT ' + SUBSTRING(@SQL, 1 + LEN(','), LEN(@SQL)) + '
FROM ' + @QName

PRINT @SQL

EXECUTE sp_executesql @SQL