identify null or empty - 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 2000 Forums

SQL Server Development (2000)

identify null or empty

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2006-12-03 : 23:07:43

hi fyi,
Iam maintaining gworker,oworker,mworker master table in one database..

gworker oworker mworker
idno idno idno
town wages skill_level
city emptype service
district

with the help of sysobjects and syscolumns table in a database,how to identify gworker.city,gworker.wages,gworker.emptype and mworker.skill_level columns is empty or null

or any other way without the above???can u suggest it??????

front end iam using is vb6.0
backend sqlserver2000
report:crystal report

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-03 : 23:49:45

Do you want to know whether column is NULLABLE or whether they actually holding NULL values?

For 1st part, query is quite simple:

select column_name, Is_Nullable from information_schema.columns where table_name = 'gworker' and column_name in ('city', 'wages', 'emptype')
union all
select column_name, Is_Nullable from information_schema.columns where table_name = 'mworker' and column_name = 'Skill_Level'

Harsh Athalye
India.
"Nothing is Impossible"

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2006-12-04 : 00:45:00

hi Mr.harsh_athalye,
I need the 2nd query.to find the value of a column which contains NULL value or empty..

quote:
Originally posted by harsh_athalye

Do you want to know whether column is NULLABLE or whether they actually holding NULL values?

For 1st part, query is quite simple:

select column_name, Is_Nullable from information_schema.columns where table_name = 'gworker' and column_name in ('city', 'wages', 'emptype')
union all
select column_name, Is_Nullable from information_schema.columns where table_name = 'mworker' and column_name = 'Skill_Level'

Harsh Athalye
India.
"Nothing is Impossible"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-04 : 01:00:50

[code]Select * from gworker
where isnull(city,'')='' or isnull(wages,'') = '' or isnull(emptype,'') = ''[/code]

[code]Select * from mworker
where isnull(Skill_Level,'') = ''[/code]

Harsh Athalye
India.
"Nothing is Impossible"

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2006-12-04 : 02:57:04

hi Mr.harsh_athalye,
txs for ur immediate reply..
what u mentioned below is applied for checking each and every single table.suppose if i want to know what all the null or '' column field in entired database.how to identify????

quote:
Originally posted by harsh_athalye

Select * from gworker
where isnull(city,'')='' or isnull(wages,'') = '' or isnull(emptype,'') = ''

Select * from mworker
where isnull(Skill_Level,'') = ''

Harsh Athalye
India.
"Nothing is Impossible"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources