troubleshooting Question

Need to identify only columns that have all NULL values in a row per each table in a database.

Avatar of stephenlecomptejr
stephenlecomptejrFlag for United States of America asked on
DatabasesMicrosoft SQL ServerSQL
32 Comments1 Solution202 ViewsLast Modified:
I want to take a SQL Server database - list out only the tables that have columns with null values on every row for that column.   (again key difference here is null values for EVERY row) - different from last question I asked.

Basically I want the list to be shown as:  

TableName, DateFound, NumberOfTotalColumnsFound, AllColumnNamesListed

The intention is to use this as a historian table and record if it occurs daily.


I found this code which takes only one table and lists the column names one at a time on top of each other:
declare @col varchar(255), @cmd varchar(max)

DECLARE getinfo cursor for
SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID
WHERE t.Name = 'USA_SETLD_GDP_W_BOEM_WELL_SURFACE'

OPEN getinfo

FETCH NEXT FROM getinfo into @col

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @cmd = 'IF NOT EXISTS (SELECT top 1 * FROM [USA_SETLD_GDP_W_BOEM_WELL_SURFACE] WHERE [' + @col + '] IS NOT NULL) BEGIN print ''' + @col + ''' end'
    EXEC(@cmd)

    FETCH NEXT FROM getinfo into @col
END

CLOSE getinfo
DEALLOCATE getinfo

at this website:
https://stackoverflow.com/questions/63291/sql-select-columns-with-null-values-only

But I need it converted where it's in a summary form and ran for all tables not just for one specific.
Again, with the data displaying like so:

TableName, DateFound, NumberOfTotalColumnsFound, AllColumnNamesListed

How do I convert or change this query to list all tables and in one row the information I need?

This question piggybacks on this other one:
https://www.experts-exchange.com/questions/29181831/Need-to-identify-only-columns-that-have-NULL-values-for-a-table.html
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 32 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 1 Answer and 32 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