12,395,645 members (64,025 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
 Jochen Arndt 195 OriginalGriff 195 Richard MacCutchan 80 ppolymorphe 70 Richard Deeming 65
 OriginalGriff 6,038 Karthik Bangalore 2,382 ppolymorphe 2,360 F-ES Sitecore 1,877 Richard MacCutchan 1,717