List empty fields - SQL Server Forums

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)

List empty fields

Author

Topic

Zifter
Yak Posting Veteran

51 Posts

Posted - 2011-09-30 : 11:01:48

Hi,

I'm trying to get a list of all missing fields.
I managed to get the expected result, but it looks rather clumsy.
The following is just an example, I have much more columns (and related tables) to check for, making it a huge query.
Is there any way to improve it?

declare @tmpTable TABLE(ID int, Name varchar(50), Street varchar(50))

insert into @tmpTable
(ID,Name,Street)
values
(1,'Aline',NULL),
(2,'Bert','Second Street'),
(3,NULL,'Third Street')


select ID,'Name'
from @tmpTable
where Name is null
union all
select ID,'Street'
from @tmpTable
where Street is null

Thanks!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-09-30 : 11:49:45

I don't think there is any significantly better way to do it - you still need to query every (nullable) column individually. You could use the information_schema.columns view to generate the code though to make your life a little easier.

Be One with the Optimizer
TG

Zifter
Yak Posting Veteran

51 Posts

Posted - 2011-10-04 : 10:32:29

Thanks TG, for the suggestion of using the information_schema.

I was hoping for an approach without all the "union all"s ... guess I'll have to live with them.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources