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

Open in new window


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
ASKER CERTIFIED SOLUTION
Pavel Celba
All in One

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 32 Comments.
Start Free Trial
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