troubleshooting Question

Need to identify only columns that have NULL values for a table

Avatar of stephenlecomptejr
stephenlecomptejrFlag for United States of America asked on
Microsoft SQL ServerSQL
5 Comments2 Solutions103 ViewsLast Modified:
I need to identify only columns that have NULL values for a table.
I'm currently using this answer to a similar question here:

https://dba.stackexchange.com/questions/14864/test-if-any-columns-are-null

with the following SQL script shown below.
It works great - however I need it in the form of a summary.

How do I change the below to only list column names that have NULLS?


DECLARE @tb nvarchar(512) = N'dbo.[WELLS]';

DECLARE @sql nvarchar(max) = N'SELECT * FROM ' + @tb
    + ' WHERE 1 = 0';

SELECT @sql += N' OR ' + QUOTENAME(name) + ' IS NULL'
    FROM sys.columns 
    WHERE [object_id] = OBJECT_ID(@tb);

EXEC sys.sp_executesql @sql;
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros