Link to home
Start Free TrialLog in
Avatar of Neilgordo
Neilgordo

asked on

finding which fields are "nullable"

hi

i'm looking for a piece of sql that will enable me to see which fields in a table can have null values.

i've tried select * from syscolumns where id=(select id FROM sysobjects where name='table_name') but this doesn't give that attribute.

i'm calling this sql from VBA, so i cannot use sp_help as the "nextrecordset" method doesn't work when using sybase

any ideas?
ASKER CERTIFIED SOLUTION
Avatar of Steve Wales
Steve Wales
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
sjwales answer will only work in a recent(ish) version of Sybase.  Iirc, bit type was added in ASE 15.  If you have an earlier version, I'd try int instead of bit (though that will result in 8 for nulls).
I think this what you need (just nullable columns):

select o.name as tableanme, c.name as columnname
from syscolumns c
join sysobjects o
on c.id = o.id
where o.type = 'U'
  and object_name(o.id) = 'Your_table'
  and status & 8 = 8
order by c.name
Avatar of Neilgordo
Neilgordo

ASKER

using latest version of sybase so solution works perfectly, thanks