?
Solved

finding which fields are "nullable"

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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

"Day by day nothing changes but when u look back, everything is different". That quote precisely describes today’s digital era. For example, you may not have noticed the change, but Voice Search is now all around us.
Google webmaster tool or Google search console is an imperative tool for solving different website relevant issues. The amazing features of this tool greatly help to check the indexing and improve the visibility of the site.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

612 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