How to figure out which columns contain data

Imagine you want to know, which columns of a database table contain data and which are empty.
To go through each column to check if any row contains data is a time-consuming task.

This script could help:

/*--------------------------------------------------------------------------------*/

DECLARE @tablename VARCHAR(50) = 'AccountBase'
 ,@schemaname VARCHAR(20) = 'dbo'
DECLARE @sqlstatement VARCHAR(max)
 ,@i INT
 ,@rowcount INT
DECLARE @temp TABLE (
 sqlstatement NVARCHAR(max)
 ,id INT
 )

INSERT INTO @temp
SELECT 'SELECT COLUMN_NAME = ''' + COLUMN_NAME + ''',

            MAX(CASE WHEN ' + COLUMN_NAME + ' IS NULL THEN 0 ELSE 1 END) AS IsFilled

            FROM [' + @schemaname + '].' + @tablename
     ,ROW_NUMBER() OVER (ORDER BY cols.COLUMN_NAME) AS rowno
FROM INFORMATION_SCHEMA.COLUMNS cols
WHERE cols.TABLE_NAME = @tablename

SELECT @rowcount = COUNT(*)
 ,@i = 1
FROM @temp

SELECT @sqlstatement = 'select * from ('

WHILE @rowcount >= @i
BEGIN
 SELECT @sqlstatement = CASE 
   WHEN @rowcount = @i
    THEN @sqlstatement + CHAR(13) + (
      SELECT sqlstatement
      FROM @temp
      WHERE id = @i
      )
   ELSE @sqlstatement + CHAR(13) + (
     SELECT sqlstatement
     FROM @temp
     WHERE id = @i
     ) + ' UNION'
   END

 SELECT @i = @i + 1
END

SELECT @sqlstatement = @sqlstatement + CHAR(13) + ') temp'

SELECT CONVERT(XML, '<XML><![CDATA[' + CHAR(13) + @sqlstatement + CHAR(13) + ']]>

    </XML>') AS [xmlWrappedSQLCommand];

/*--------------------------------------------------------------------------------*/

The result is a executable script. Just copy it into a new query window to execute it.

Result (XML):

Script Execution Result: