Get columns of a table: only columns with values

If you require retrieving a table’s column list, specifically those containing values, you can do so

DECLARE @TableName NVARCHAR(128) = 'BFDOCD'; 

DECLARE @SQLQuery NVARCHAR(MAX) = '';

SELECT @SQLQuery = @SQLQuery +
    CASE 
        WHEN DATA_TYPE IN ('int', 'smallint', 'bigint', 'decimal', 'numeric', 'float', 'real') THEN 
            'SELECT ''' + COLUMN_NAME + ''' AS ColumnName FROM ' + @TableName + ' WHERE ' + QUOTENAME(COLUMN_NAME) + ' IS NOT NULL AND ' + QUOTENAME(COLUMN_NAME) + ' != 0 UNION '
        ELSE
            'SELECT ''' + COLUMN_NAME + ''' AS ColumnName FROM ' + @TableName + ' WHERE ' + QUOTENAME(COLUMN_NAME) + ' IS NOT NULL UNION '
    END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND TABLE_SCHEMA = 'dbo' -- Modificare se lo schema non è dbo

SET @SQLQuery = LEFT(@SQLQuery, LEN(@SQLQuery) - 6); -- Rimuove l'ultimo UNION

EXEC sp_executesql @SQLQuery;