Click here to Skip to main content
15,904,153 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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?
Thank you for all reply,
but i don't know how many number of columns are there.
Posted
Updated 18-Mar-13 3:00am
v3
Comments
MalwareTrojan 21-Feb-13 4:12am    
dint got your question. . . .

Hi ,

Use COALESCE() Function like as follows

SQL
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
 
Share this answer
 
v3
Comments
Maciej Los 5-Apr-13 6:19am    
Why 1 star?
It should works... +5!
For unknow number o columns, try this:
SQL
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)!
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900