13,050,450 members (85,274 online)
Rate this:
See more:
We have a table named "TEST" with 4 columns (Col1, Col2, Col3, Col4,.........,coln) with following data -

1, Test, Exam, Online ...........
NULL, NULL, NULL, NULL
2, NULL, Practice, NULL
NULL, NULL, NULL, NULL
NULL, NULL, NULL, NULL
NULL, NULL, NULL, NULL

How can i check the column which having all null values?
but i don't know how many number of columns are there.
Posted 20-Feb-13 19:26pm
Updated 18-Mar-13 3:00am
v3
Vardhan Desai 21-Feb-13 4:12am

dint got your question. . . .

Rate this:

## Solution 1

Hi ,

Use COALESCE() Function like as follows

```CREATE TABLE #Test (Col1 INT, Col2 INT, Col3 INT, Col4 INT)

INSERT INTO #Test(Col1,Col2,COl3, Col4) VALUES(NULL,1,NULL,NULL),(NULL,NULL,NULL,NULL)
INSERT INTO #Test(Col1,Col2,COl3, Col4) VALUES(NULL,NULL,2,NULL),(NULL,NULL,NULL,NULL)
INSERT INTO #Test(Col1,Col2,COl3, Col4) VALUES(NULL,NULL,NULL,3),(NULL,NULL,NULL,NULL)

SELECT Col1,Col2,Col3, Col4 FROM #Test WHERE COALESCE(Col1, Col2, Col3, Col4) IS NULL```

Regards,
GVPrabu
v3
Maciej Los 5-Apr-13 6:19am

Why 1 star?
It should works... +5!
Rate this:

## Solution 3

For unknow number o columns, try this:
```USE DataBaseName;

DECLARE @cols NVARCHAR(2000)
DECLARE @tblName NVARCHAR(30)
DECLARE @sql NVARCHAR(MAX)

SET @tblName = 'TableName'
SET @cols = STUFF((SELECT DISTINCT '],[' + syscolumns.name
FROM sysobjects INNER JOIN syscolumns  ON sysobjects.id = syscolumns.id
WHERE (sysobjects.xtype = 'u' and sysobjects.name = @tblName)
--ORDER BY '],[' + syscolumns.colid
FOR XML PATH('')),1,2,'') + ']'

SELECT @cols AS [columns]

SET @sql = 'SELECT ' + @cols + ' ' +
'FROM ' + @tblName + ' ' +
'WHERE COALESCE(' + @cols + ') IS NULL'
EXEC (@sql)```

RISK: Length of string reserved for column names - stored in `@cols` variable - can exceed declared value (2000)!

Top Experts
Last 24hrsThis month
 OriginalGriff 323 Sheila Pontes 110 Graeme_Grant 91 Kornfeld Eliyahu Peter 70 omerkamran 40
 OriginalGriff 5,271 RickZeeland 1,944 ppolymorphe 1,758 F-ES Sitecore 1,566 Dave Kreskowiak 1,389