SQL Server - List only NULL columns in a table

Sometimes when you have a database with massive tables (in the order of millions of rows) getting fed by multiple sources, it is very difficult to find out which columns are completely empty (or contain the default NULL value).

I am pretty sure that I wasn't the only one who came against this issue, so in order to save others some time, you can find below a snippet of code that I wrote where it prints out all the columns of your table that contain only NULL values for all rows.

DECLARE @NAME sysname
DECLARE CRS CURSOR

LOCAL FAST_FORWARD FOR
SELECT name FROM syscolumns
WHERE id=OBJECT_ID('<TABLE_NAME>')

OPEN CRS
FETCH NEXT FROM CRS INTO @NAME

WHILE @@FETCH_STATUS = 0
BEGIN

      EXEC('SELECT ''' + @NAME + '''
            WHERE NOT EXISTS
                  (SELECT * FROM <TABLE_NAME>
                  WHERE ' + @NAME + ' IS NOT NULL)'
      )

      FETCH NEXT FROM CRS INTO @NAME

END

CLOSE CRS
DEALLOCATE CRS

Hope you found this helpful and that it will make your life a bit easier.

Till next time...