How Determine Fields That Are All Nulls

Question

Hi Community,

Can someone let me know how to write code that will find fields that contain all NULLs?

For example, in the following table field 'client_surname' and field 'state' contain all NULLs.

Can someone let me know the code to determine those fields containing all nulls.

Sample data

CREATE TABLE #tmpTable (  
    client_id int,  
    client_name varchar(500),  
    client_surname varchar(500),  
    city varchar(500),  
    state varchar(500))  
  
INSERT #tmpTable VALUES  
(1,'Miriam',NULL,'Las Vegas',NULL),  
(2,'Astrid',NULL,'Chicago',NULL),  
(3,'David',NULL,'Phoenix',NULL),  
(4,'Hiroki',NULL,'Orlando',NULL)  
  
SELECT * FROM #tmpTable  

Answer 1

Hi @Carlton Patterson ,

Please try the following solution.
It is using SQL Server XML and XQuery powers without dynamic SQL and cursors.

The algorithm is very simple.
When we are converting each row into XML, columns that hold NULL value are missing from the XML.
After that set operator EXCEPT brings us those missing columns comparing them with the full set of columns of the table.

SQL

USE tempdb;  
GO  
  
DROP TABLE IF EXISTS #tmpTable;  
  
CREATE TABLE #tmpTable (  
     client_id int,  
     client_name varchar(500),  
     client_surname varchar(500),  
     city varchar(500),  
     state varchar(500));  
      
INSERT #tmpTable VALUES  
(1,'Miriam',NULL,'Las Vegas',NULL),  
(2,'Astrid',NULL,'Chicago',NULL),  
(3,'David',NULL,'Phoenix',NULL),  
(4,'Hiroki',NULL,'Orlando',NULL);  
  
SELECT name   
FROM tempdb.sys.columns  
WHERE object_id = object_id('tempdb..#tmpTable')  
 AND is_nullable = 1  
EXCEPT  
SELECT x.value('local-name(.)', 'SYSNAME')  
FROM #tmpTable AS t  
 CROSS APPLY (SELECT t.* FOR XML PATH(''), TYPE, ROOT('root')) AS t1(c)  
 CROSS APPLY c.nodes('/root/*') AS t2(x);  

Output

+----------------+  
|      name      |  
+----------------+  
| client_surname |  
| state          |  
+----------------+  

Answer 2

You will need to run query per column, but you can compose these with dynamic SQL:

   DECLARE @sql      nvarchar(MAX),  
           @cur       CURSOR,  
           @col       sysname,  
           @hasnonnull bit  
     
   SET @cur = CURSOR STATIC FOR  
      SELECT name   
      FROM   tempdb.sys.columns  
      WHERE  object_id = object_id('tempdb..#tmpTable')  
        AND  is_nullable = 1  
     
   OPEN @cur  
     
   WHILE 1 = 1  
   BEGIN  
      FETCH @cur INTO @col  
      IF @@fetch_status <> 0  
         BREAK  
     
      SELECT @sql = 'SELECT @flag =  
                        CASE WHEN EXISTS (  
                              SELECT * FROM #tmpTable   
                              WHERE ' + quotename(@col) + ' IS NOT NULL)  
                             THEN 1  
                             ELSE 0  
                        END'  
         
      EXEC sp_executesql @sql, N'@flag bit OUTPUT', @hasnonnull OUTPUT  
     
      IF @hasnonnull = 0  
         PRINT @col  
   END  

Answer 3

Hi Erland,

This is very strange.

The table that I'm executed your code on is as follows:

When execute your code on the table I get the following output

As you can see, no printout of the results

Answer 4

ok, not sure what I'm doing wrong.

I have executed code from both Erland and YitzhakKhabinsky-0887, but I'm not getting a successful result - I executed both on SSMS

and