Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
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 20-Feb-13 20:26pm
Edited 18-Mar-13 4:00am
v3
Comments
Vardhan Desai at 21-Feb-13 4:12am
   
dint got your question. . . .
Rate this: bad
good
Please Sign up or sign in to vote.

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
  Permalink  
v3
Comments
Maciej Los at 5-Apr-13 6:19am
   
Why 1 star?
It should works... +5!
Rate this: bad
good
Please Sign up or sign in to vote.

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)!
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 350
1 Jochen Arndt 190
2 Richard MacCutchan 135
3 Sergey Alexandrovich Kryukov 120
4 DamithSL 95
0 OriginalGriff 6,045
1 DamithSL 4,611
2 Maciej Los 4,087
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,310


Advertise | Privacy | Mobile
Web02 | 2.8.141220.1 | Last Updated 5 Apr 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100