Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

finding which fields are "nullable"

Posted on 2013-07-01
4
Medium Priority
?
916 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 23

Accepted Solution

by:
Steve Wales earned 600 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 27

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Your business may be under attack from a silent enemy that is hard to detect. It works stealthily in the shadows to access and exploit your critical business information, sensitive confidential data and intellectual property, for commercial gain. T…
There can be many situations demanding the conversion of Outlook OST files to PST format and as such, there is no shortage of automated tools to perform this conversion. However, what makes Stellar OST to PST converter stand above the rest? Let us e…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…

916 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