Count the Null columns in a row in SQL

My answer builds on Michael Berkowski's answer, but to avoid having to type out hundreds of column names, what I did was this:

Step 1: Get a list of all of the columns in your table

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTable';

Step 2: Paste the list in Notepad++ (any editor that supports regular expression replacement will work). Then use this replacement pattern

• Search:

  ^(.*)$
  

• Replace:

  \(CASE WHEN \1 IS NULL THEN 1 ELSE 0 END\) +
  

Step 3: Prepend SELECT identityColumnName, and change the very last + to AS NullCount FROM myTable and optionally add an ORDER BY...

SELECT 
    identityColumnName, 
    (CASE WHEN column001 IS NULL THEN 1 ELSE 0 END) +
    -- ...
    (CASE WHEN column200 IS NULL THEN 1 ELSE 0 END) AS NullCount
FROM
    myTable
ORDER BY 
    NullCount DESC

This method assigns a 1 or 0 for null columns, and adds them all together. Hopefully you don't have too many nullable columns to add up here...

SELECT 
  ((CASE WHEN col1 IS NULL THEN 1 ELSE 0 END)
  + (CASE WHEN col2 IS NULL THEN 1 ELSE 0 END)
  + (CASE WHEN col3 IS NULL THEN 1 ELSE 0 END)
  ...
  ...
  + (CASE WHEN col10 IS NULL THEN 1 ELSE 0 END)) AS sum_of_nulls
FROM table
WHERE Customer=some_cust_id

Note, you can also do this perhaps a little more syntactically cleanly with IF() if your RDBMS supports it.

SELECT 
  (IF(col1 IS NULL, 1, 0)
  + IF(col2 IS NULL, 1, 0)
  + IF(col3 IS NULL, 1, 0)
  ...
  ...
  + IF(col10 IS NULL, 1, 0)) AS sum_of_nulls
FROM table
WHERE Customer=some_cust_id

I tested this pattern against a table and it appears to work properly.