Click here to Skip to main content
12,070,171 members (29,553 online)
Rate this:
 
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 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 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
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web03 | 2.8.160208.1 | Last Updated 5 Apr 2013
Copyright © CodeProject, 1999-2016
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