0

Is there an easy way to count nulls in all fields in a table without writing 40+ very similar, but slightly different, queries? I would think there is some kind of statistics maintained for all tables, and this may be the easiest way to go with it, but I don't know for sure. Thoughts, anyone? Thanks!!

BTW, I am using SQL Server 2008.

ASH
  • 20,759
  • 19
  • 87
  • 200

3 Answers3

1
 SELECT COUNT( CASE WHEN field01 IS NULL THEN 1 END) +
        COUNT( CASE WHEN field02 IS NULL THEN 1 END) +
        ... 
        COUNT( CASE WHEN field40 IS NULL THEN 1 END) as total_nulls
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • Thanks Kaepora but I have read-rights only. That could work Juan. Finally, @squillman, I am curious to see how your script works. When I enter my table name, I get this error message: Msg 139, Level 15, State 1, Line 0 Cannot assign a default value to a local variable. Msg 137, Level 15, State 2, Line 10 Must declare the scalar variable "@table". – ASH Nov 03 '17 at 18:23
1

This answer will return a table containing the name of each column of a specified table. (@tab is the name of the table you're trying to count NULLs in.)

You can loop through the column names, count NULLs in each column, and add the result to a total running count.

Xeraqu
  • 195
  • 2
  • 13
1

Not sure if you consider this simple or not, but this will total the NULLs by column in a table.

DECLARE @table sysname;
SET @table = 'MyTable'; --replace this with your table name
DECLARE @colname sysname;
DECLARE @sql NVARCHAR(MAX);

DECLARE COLS CURSOR FOR
SELECT c.name
FROM sys.tables t
INNER JOIN sys.columns c ON t.object_id = c.object_id
WHERE t.name = @table;

SET @sql = 'SELECT ';

OPEN COLS;
FETCH NEXT FROM COLS INTO @colname;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = @sql + 'COUNT(CASE WHEN ' + @colname + ' IS NULL THEN 1 END) AS ' + @colname + '_NULLS,'
    FETCH NEXT FROM COLS INTO @colname;
END;

CLOSE COLS;
DEALLOCATE COLS;

SET @sql = LEFT(@sql,LEN(@sql) - 1) --trim tailing ,
SET @sql = @sql + ' FROM ' + @table;

EXEC sp_executesql @sql;
squillman
  • 13,363
  • 3
  • 41
  • 60
  • I am curious to see how your script works. When I enter my table name, I get this error message: Msg 139, Level 15, State 1, Line 0 Cannot assign a default value to a local variable. Msg 137, Level 15, State 2, Line 10 Must declare the scalar variable "@table". – ASH Nov 03 '17 at 18:37
  • Whoops, I was wrong. I am actually using SQL Server 2005. I think that is part of the problem, squillman. How can I modify your script to work on 2005? – ASH Nov 03 '17 at 19:07
  • You could do this without using a cursor. – Sean Lange Nov 03 '17 at 19:11
  • Yeah! That works. I Googled it and thought it was something like that, but I didn't know exactly what to fix. Now, I am trying to add a date range, with the field name 'ASOFDATE' but I can't get it working. I'll try again tomorrow. Thanks for the help with this. – ASH Nov 03 '17 at 21:30