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 FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID

OPEN getinfo

FETCH NEXT FROM getinfo into @col

    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'

    FETCH NEXT FROM getinfo into @col

CLOSE getinfo

Open in new window

at this website:

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