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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
using latest version of sybase so solution works perfectly, thanks