As others have said, neither CURSORS or WHILE LOOPS are effective. Here's a better solution that might help with this problem. I'm just publishing this to avoid that others would stay with the RBAR solution.
I'm including two possible solutions depending on what is needed. Can you understand it, yuvipoy?
USE Test
GO
IF EXISTS( SELECT 1 FROM sys.tables WHERE name = 'Test')
DROP TABLE Test
CREATE TABLE Test(
IDintIDENTITY,
Col1int NULL,
Col2int NULL,
Col3int NULL,
Col4int NULL,
Col5int NULL,
Col6int 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