Solved

finding which fields are "nullable"

Posted on 2013-07-01
4
832 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
With the rising number of cyber attacks in recent years, keeping your personal data safe has become more important than ever. The tips outlined in this article will help you keep your identitfy safe.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

623 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