How Determine Fields That Are All Nulls

Question

How Determine Fields That Are All Nulls

Carlton Patterson 761

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

0 comments

Answer accepted by question author

Yitzhak Khabinsky 27,196

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          |  
+----------------+

Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2022-12-25T20:38:47.67+00:00

Amazing! My head is still spinning.

3 additional answers

Your answer

Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2022-12-25T20:38:47.67+00:00

Amazing! My head is still spinning.

Answer 1

Erland Sommarskog 134.7K MVP Volunteer Moderator

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

Carlton Patterson 761 Reputation points

2022-12-25T17:28:21.203+00:00

Wow,
Thanks Erland.

Going to check this out shortly

Just wanted to quickly say thanks
Carlton Patterson 761 Reputation points

2022-12-25T17:48:07.567+00:00

Hi Erland,

Just executed your code, the code executed successfully, but I didn't get any printed output.

Am I doing something wrong?

Carlton Patterson 761

Hi Erland,

I modified your code to replace #tmpTable with a table called clients

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..clients')  
     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 clients   
                           WHERE ' + quotename(@col) + ' IS NOT NULL)  
                          THEN 1  
                          ELSE 0  
                     END'  
      
   EXEC sp_executesql @sql, N'@flag bit OUTPUT', @hasnonnull OUTPUT  
   IF @hasnonnull = 1  
      PRINT @col  
END

As I mentioend, it executed successfully, but no output.

Erland Sommarskog 134.7K MVP Volunteer Moderator

Maybe there is no column with all NULL in that table?

When I ran your code with the table you posted it produced output. Here is the full script:`

   CREATE TABLE #tmpTable (  
      client_id int   NOT NULL,  
      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)  
   go  
   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 = 1  
         PRINT @col  
   END  
   go  
   DROP TABLE #tmpTable

Also, beware that since I'm using PRINT the output comes in the Messages tab.

Anonymous

2022-12-26T09:30:46.573+00:00

Hello ErlandSommarskog.
Am I wrong or is the final test wrong?
It should be <> 1
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2022-12-26T09:33:23.667+00:00

<SPAN STYLE="redCheeks">
Oops... I guess I need to edit my post. Thanks for pointing it out.
</SPAN>

Answer 2

Carlton Patterson 761

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

Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2022-12-25T22:20:07.657+00:00

Is your clients table in tempdb? Judging from the OE on the left it is not.

Here is the kicker: you cannot treat the solutions you get here as black boxes. You should look to what is inside, so that you can do the necessary adaptions to your actual problem.

Answer 3

Carlton Patterson 761

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

Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2022-12-25T20:10:57.663+00:00

I am not familiar with the query tool you are using, so I can't say how it is dealing with PRINT output. (But I would check the other two tabs.)

Trying running my script in SSMS instead.
Viorel 127K Reputation points

2022-12-25T20:26:46.667+00:00

Maybe you should use a correct name in object_id('tempdb..clients'). For example, try object_id('clients'), and also use sys.columns instead of tempdb.sys.columns.

Share via

How Determine Fields That Are All Nulls

3 additional answers

Your answer