How to remove empty columns from query output

Transact-SQL
3

That's a rather extreme blanket statement to make.

I added an empty column to your sample table, otherwise how would we know if the code successfully removed a column(s)? Uncomment ColD to test removing multiple columns at once.


DROP TABLE IF EXISTS #tmpTable;
CREATE TABLE #tmpTable (
    as_of_date date NOT NULL,
    ColA varchar(50) NULL,
    ColB varchar(50) NULL,
    ColC varchar(10) NULL /*, ColD int NULL */ ) --<<--
INSERT #tmpTable ( as_of_date, ColA, ColB ) VALUES
    (CONVERT(DATETIME, '2023-05-31', 120),'LM','A'),
    (CONVERT(DATETIME, '2023-03-31', 120),'LQ','B'),
    (CONVERT(DATETIME, '2021-12-31', 120),'LY','C'),
    (CONVERT(DATETIME, '2023-04-30', 120),'LM2','D'),
    (CONVERT(DATETIME, '2022-09-30', 120),'LQ2','E'),
    (CONVERT(DATETIME, '2021-12-31', 120),'LY2','F')

SELECT 'before DROP COLUMN(s)', * FROM #tmpTable;

--******************************************************************************

DECLARE @column_list nvarchar(max)
DECLARE @sql nvarchar(max)

SELECT @sql = (SELECT 
    REPLACE('+CASE WHEN COUNT(*) = SUM(CASE WHEN [$column$] IS NULL ' +
        'THEN 1 ELSE 0 END) THEN '',[$column$]'' ELSE '''' END', 
        '$column$', c.name)
    FROM tempdb.sys.columns c
    WHERE c.is_nullable = 1 AND c.is_computed = 0 AND c.object_id = OBJECT_ID('tempdb.dbo.#tmpTable')
    ORDER BY c.column_id
    FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)')

SELECT @sql = 'SELECT @column_list = ' + STUFF(@sql, 1, 1, '') + ' FROM #tmpTable'

EXEC sys.sp_executesql @sql, N'@column_list nvarchar(max) OUTPUT', @column_list OUTPUT

IF LEN(@column_list) > 1
BEGIN
    SET @sql = 'ALTER TABLE #tmpTable DROP COLUMN ' + STUFF(@column_list, 1, 1, '')
    SELECT @sql
    EXEC(@sql)
    SELECT 'after DROP COLUMN(s)', * FROM #tmpTable
END /*IF*/

--******************************************************************************