Get all rows of table having NULL in all columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Get all rows of table having NULL in all columns

Author

Topic

bsethi24
Starting Member

25 Posts

Posted - 2012-03-27 : 10:52:37

Dear All,

I am looking for a solution without compromising on Performance & without writing all column names to get all rows those have NULL or BLANK in their all columns.

For example,

We have a table named "TEST" with 4 columns (Col1, Col2, Col3, Col4) with following data -

1, Test, Exam, Online
NULL, NULL, NULL, NULL
2, Course, Practice, Improvement
NULL, NULL, NULL, NULL
NULL, NULL, NULL, NULL
NULL, NULL, NULL, NULL

Now if I need to extract 2nd, 4th, 5th and 6th rows then as per me I have to write

Select * from TEST where
((Col1 is null) and (Col2 is null) and (Col3 is null) and (Col4 is null))

OR if table has some rows with all columns = '' (BLANK) and some rows with all columns = NULL then

Select * from TEST where
((Col1 is null or Col1 = '') and (Col2 is null or Col2 = '') and (Col3 is null or Col3 = '') and (Col4 is null or Col4 = ''))

Is there any smarter way to achieve the same???

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-27 : 11:42:27

Are you saying:

I want to show ALL rows where ALL the columns are null or have spaces?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-03-27 : 11:55:26

quote:
Originally posted by bsethi24

Select * from TEST where
((Col1 is null or Col1 = '') and (Col2 is null or Col2 = '') and (Col3 is null or Col3 = '') and (Col4 is null or Col4 = ''))

That's the way.

--
Gail Shaw
SQL Server MVP

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-27 : 12:23:15

I assuming you want to be lazy



SELECT SQL_LINE FROM (
	SELECT 'SELECT * FROM [' + TABLE_CATALOG + '].[' + TABLE_SCHEMA + '].[' +  TABLE_NAME + ']' AS SQL_LINE
			, 1 AS SQL_ORDER, ORDINAL_POSITION, TABLE_NAME
	  FROM INFORMATION_SCHEMA.COLUMNS
	 WHERE ORDINAL_POSITION = 1
 UNION ALL
 	SELECT 'WHERE (' + COLUMN_NAME + ' IS NULL OR ' + COLUMN_NAME + ' = ' + '''' + '''' + ')'  AS SQL_LINE
			, 2 AS  SQL_ORDER, ORDINAL_POSITION, TABLE_NAME
	  FROM INFORMATION_SCHEMA.COLUMNS
	 WHERE ORDINAL_POSITION = 1
 UNION ALL
 	SELECT '  AND (' + COLUMN_NAME + ' IS NULL OR ' + COLUMN_NAME + ' = ' + '''' + '''' + ')'  AS SQL_LINE
			, 2 AS  SQL_ORDER, ORDINAL_POSITION, TABLE_NAME
	  FROM INFORMATION_SCHEMA.COLUMNS
	 WHERE ORDINAL_POSITION <> 1
 UNION ALL
 	SELECT 'GO'  AS SQL_LINE
			, 3 AS  SQL_ORDER, ORDINAL_POSITION, TABLE_NAME
	  FROM INFORMATION_SCHEMA.COLUMNS
	 WHERE ORDINAL_POSITION = 1
) AS XXX
ORDER BY TABLE_NAME, SQL_ORDER,ORDINAL_POSITION

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-27 : 12:27:43

do you've a PK in your table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources