Find out all the columns having Null values in the table in SQL

Question

I am using Sql Server 2008. In one of my table's columns having null values. Now I want to find out all the columns that have null value in the table using query.

So how to find out all the columns which have null values in the table in SQL?

Take a peek at this: http://stackoverflow.com/questions/63291/sql-select-columns-with-null-values-only — g00dy, Feb 27 '13 at 08:08

score 0 · Accepted Answer · edited Apr 13 '17 at 12:42

Suppose your table is

CREATE TABLE T
(
A INT PRIMARY KEY,
B CHAR(1000) NULL,
C CHAR(1000) NULL
)

One way would be

SELECT 
    MAX(CASE WHEN B IS NULL THEN 1 ELSE 0 END) AS B,
    MAX(CASE WHEN C IS NULL THEN 1 ELSE 0 END) AS C
FROM T

This is simple but potentially could end up scanning the whole table unnecessarily. Even if the first row scanned contains NULL for both columns.

A query that potentially avoids that is

SELECT DISTINCT TOP 2 NullExists
FROM test T 
CROSS APPLY (VALUES(CASE WHEN b IS NULL THEN 'b' END),
                   (CASE WHEN c IS NULL THEN 'c' END)) V(NullExists)
WHERE NullExists IS NOT NULL

Execution plans and some alternative approaches are in my question here.

thanks @Martin Smith. that's i want. – Brijesh Patel Feb 27 '13 at 08:20 — Brijesh Patel, Feb 27 '13 at 08:20

Find out all the columns having Null values in the table in SQL

1 Answers1