How to get column list having null values

Question

How to get column list having null values

Table1
=====

ID Name City Email
1 N1 e1
2 N2 C2
3 N3 C3 e3

I need a query to find those columns whose rows having null values.

Above, o/p will be City and Email

Don't want to use the conditions like City is null or email is null

John Penn · Answer

Oh... NOW you tell me. ;) The overall principle is still sound though. You'll need to use the Oracle metadata to iterate through the columns and execute some dynamic sql to check for nulls on each column. I'm not a PL/SQL expert so I can't help you there sorry.

Suthish Nair · Answer

Sorry John Its Oracle..

John Penn · Answer

This will work... I found some code on another site (see the 1st response to the post) but it didn't work so I modified it a bit: declare @col varchar(255) @cmd varchar(max) @tablename varchar(50) set @tablename = 'Table1' create table #nullcols (colname varchar(50)) DECLARE getinfo cursor for SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID WHERE t.Name = @tablename OPEN getinfo FETCH NEXT FROM getinfo into @col WHILE @@FETCH_STATUS = 0 BEGIN SELECT @cmd = 'IF EXISTS (SELECT top 1 * FROM ' + @tablename + ' WHERE [' + @col + '] IS NULL) BEGIN insert into #nullcols values (''' + @col + ''') end' EXEC(@cmd) FETCH NEXT FROM getinfo into @col END CLOSE getinfo DEALLOCATE getinfo select * from #nullcols drop table #nullcols