Solved

finding which fields are "nullable"

Posted on 2013-07-01
4
722 Views
Last Modified: 2013-07-01
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?
0
Comment
Question by:Neilgordo
4 Comments
 
LVL 22

Accepted Solution

by:
Steve Wales earned 150 total points
ID: 39291209
Sybase documentation on the syscolumns table says that bit 3 of the status column us used to define if a column is nullable or not:

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36274.1570/html/tables/X14340.htm

Searching a little more from there I found this:

http://database.ittoolbox.com/groups/technical-functional/sybase-l/sql-server-isnullable-equivalent-in-sybase-404087

With this code snippet:

select o.name as tableanme, c.name as columnname, convert(bit, (status & 8)) as nulls
from syscolumns c
join sysobjects o
on c.id = o.id
where o.type = 'U'
order by o.name, c.name

Open in new window


Give that a shot and see if it works for you.
0
 
LVL 26

Expert Comment

by:wilcoxon
ID: 39291293
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).
0
 
LVL 13

Expert Comment

by:alpmoon
ID: 39291543
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
0
 

Author Closing Comment

by:Neilgordo
ID: 39292274
using latest version of sybase so solution works perfectly, thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: This is the second blog post in a series on email clearinghouses (https://www.xmatters.com/alert-management/blog-email-has-failed-us?utm_campaign=70138000000ydLoAAI&utm_source=exex&utm_medium=article&utm_content=blog-post).   Every month t…
With the rapid rise in mobile usage, mobile devices are here to stay and have become an integral part of doing business. Here are 9 great apps for your BYOD environment.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now