CREATE TABLE TEMP (
	id INT
	,C1 INT
	,C2 INT
	,C3 INT
	,C4 INT
	,C5 INT
	,C6 INT
	)
GO

INSERT INTO TEMP (
	id
	,C1
	,C3
	,C5
	)
VALUES (
	1
	,2
	,3
	,5
	)

INSERT INTO TEMP (
	id
	,C1
	,C3
	,C4 --changed from C5
	)
VALUES (
	1
	,6
	,7
	,8
	)

SELECT *
FROM TEMP
GO

DECLARE @name VARCHAR(100)
DECLARE @SQL1 NVARCHAR(max)
DECLARE @sqlCommand NVARCHAR(max)
DECLARE @OUPUT VARCHAR(max)
DECLARE @flg INT

SET @flg = 0
SET @OUPUT = ''

DECLARE db_cursor CURSOR
FOR
SELECT NAME
FROM sys.syscolumns
WHERE id IN (
		SELECT id
		FROM sys.sysobjects
		WHERE NAME = 'TEMP '
		)

OPEN db_cursor

FETCH NEXT
FROM db_cursor
INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @sqlCommand = 'SELECT @flg=1 from TEMP where LEN( ' + @name + ')' + '> 0 '

	print @sqlCommand
	print @flg
	EXECUTE sp_executesql @sqlCommand
		,N'@name nvarchar(100),@flg int =0 OUTPUT'
		,@name = @name
		,@flg = @flg OUTPUT

	IF @flg = 1
		SET @OUPUT = @OUPUT + @name + ', '
	SET @flg = 0

	FETCH NEXT
	FROM db_cursor
	INTO @name
END

CLOSE db_cursor

DEALLOCATE db_cursor

SET @SQL1 = 'SELECT ' + LEFT(@OUPUT, LEN(@OUPUT) - 1) + ' FROM TEMP '

EXEC SP_EXECUTESQL @SQL1
GO

DROP TABLE TEMP

USE Test
GO

IF EXISTS( SELECT 1 FROM sys.tables WHERE name = 'Test')
	DROP TABLE Test

CREATE TABLE Test(
	ID	int	IDENTITY,
	Col1	int NULL,
	Col2	int NULL,
	Col3	int NULL,
	Col4	int NULL,
	Col5	int NULL,
	Col6	int NULL)

INSERT INTO Test VALUES
( 1, NULL, 3, NULL, 4, NULL),
( 2, 7, NULL, 6, NULL, NULL)

SELECT STUFF( CASE WHEN ID IS NOT NULL THEN ', ID' ELSE '' END
		+ CASE WHEN Col1 IS NOT NULL THEN ', Col1' ELSE '' END
		+ CASE WHEN Col2 IS NOT NULL THEN ', Col2' ELSE '' END
		+ CASE WHEN Col3 IS NOT NULL THEN ', Col3' ELSE '' END
		+ CASE WHEN Col4 IS NOT NULL THEN ', Col4' ELSE '' END
		+ CASE WHEN Col5 IS NOT NULL THEN ', Col5' ELSE '' END
		+ CASE WHEN Col6 IS NOT NULL THEN ', Col6' ELSE '' END, 1,1, '') AS Not_Nulls,
	* 
FROM Test

SELECT STUFF( CASE WHEN COUNT( ID) = COUNT(*) THEN ', ID' ELSE '' END
		+ CASE WHEN COUNT( Col1) = COUNT(*) THEN ', Col1' ELSE '' END
		+ CASE WHEN COUNT( Col2) = COUNT(*) THEN ', Col2' ELSE '' END
		+ CASE WHEN COUNT( Col3) = COUNT(*) THEN ', Col3' ELSE '' END
		+ CASE WHEN COUNT( Col4) = COUNT(*) THEN ', Col4' ELSE '' END
		+ CASE WHEN COUNT( Col5) = COUNT(*) THEN ', Col5' ELSE '' END
		+ CASE WHEN COUNT( Col6) = COUNT(*) THEN ', Col6' ELSE '' END, 1,1, '') AS Not_Nulls,
	STUFF( CASE WHEN COUNT( ID) = 0 THEN ', ID' ELSE '' END
		+ CASE WHEN COUNT( Col1) = 0 THEN ', Col1' ELSE '' END
		+ CASE WHEN COUNT( Col2) = 0 THEN ', Col2' ELSE '' END
		+ CASE WHEN COUNT( Col3) = 0 THEN ', Col3' ELSE '' END
		+ CASE WHEN COUNT( Col4) = 0 THEN ', Col4' ELSE '' END
		+ CASE WHEN COUNT( Col5) = 0 THEN ', Col5' ELSE '' END
		+ CASE WHEN COUNT( Col6) = 0 THEN ', Col6' ELSE '' END, 1,1, '') AS Only_Nulls
FROM Test

Bhuvnesh (12/31/2012)

---

Yes, i have also heard this so many times that RBAR gives the result in much optimal way btu BAD thing is i never got any chance to use it (actually i always keep my self away from it, as i found it a bit complex to frame and implement it ) but here in your example, the output is not matching with solution provided above by "Sean Lange".if you help us to frame the query acc to "Sean Lange" result then it will be another good/alternative solution.

DECLARE @SQL2	nvarchar(300)
SELECT @SQL2 = 'SELECT ' + STUFF( CASE WHEN COUNT(ID) > 0 THEN ', ID' ELSE '' END
		+ CASE WHEN COUNT(C1) > 0 THEN ', C1' ELSE '' END
		+ CASE WHEN COUNT(C2) > 0 THEN ', C2' ELSE '' END
		+ CASE WHEN COUNT(C3) > 0 THEN ', C3' ELSE '' END
		+ CASE WHEN COUNT(C4) > 0 THEN ', C4' ELSE '' END
		+ CASE WHEN COUNT(C5) > 0 THEN ', C5' ELSE '' END
		+ CASE WHEN COUNT(C6) > 0 THEN ', C6' ELSE '' END, 1,1, '') + ' FROM Temp'
FROM Temp
-- PRINT @SQL2
EXEC SP_EXECUTESQL @SQL2