Solved

finding which fields are "nullable"

Posted on 2013-07-01
4
688 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Learn to move / copy / export exchange contacts to iPhone without using any software. Also see the issues in configuration of exchange with iPhone to migrate contacts.
In  today’s increasingly digital world, managed service providers (MSPs) fight for their customers’ attention, looking for ways to make them stay and purchase more services. One way to encourage that behavior is to develop a dependable brand of prod…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

707 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

19 Experts available now in Live!

Get 1:1 Help Now