RE: Help required quickly.....Showing Informix NOT NULL constrain
Might need to break it up into two queries . . . . use yours as the
basis
for primary keys and indices
Use this one for column level stuff...
select a.tabname as TABLE_NAME,
c.colname as COLUMN_NAME,
b.constrname as CONSTRAINT_NAME,b.constrtype as CONSTRAINT_TYPE
from systables a,
sysconstraints b,
syscolumns c,
syscoldepend d
where a.tabid=b.tabid
and c.tabid = d.tabid
and c.colno = d.colno
and d.constrid = b.constrid
and constrtype = "N"
and a.tabid=c.tabid
and a.tabname not like "sys%"
and a.tabid > 100
order by 1,2
John Carlson
EDS - WHSmith USA
3200 Windy Hill Road
Atlanta, GA 30339
Phone:+1-770-618-4776
mailto:john.carlson@eds.com
mailto:john_carlson@whsmithnusa.com
-----Original Message-----
From: Reeves, Dam.... [mailto:damion.reeves@eds.com]
Sent: Thursday, October 16, 2003 7:30 PM
To: ids@iiug.org
Subject: Help required quickly.....Showing Informix NOT NULL constraints
[2037]
Hi,
We are running IDS 9.21.FC3X-1 on HP-UX 11.0.
I run the following SQL against our production database to try and display
Primary Keys, Indexes and NOT NULL constraints, but only the Primary Keys
and associated Indexes are displayed.
Does anyone know how to also show the NOT NULL constraints ?
select
a.tabname as TABLE_NAME,
c.colname as COLUMN_NAME,
b.constrname as CONSTRAINT_NAME,
b.constrtype as CONSTRAINT_TYPE,
b.idxname as INDEX_NAME
from
systables a,
sysconstraints b,
syscolumns c,
sysindexes d
where
a.tabid=b.tabid
and constrtype in ("P","N")
and a.tabid=c.tabid
and b.idxname = d.idxname
and
(d.part1=c.colno or
d.part2=c.colno or
d.part3=c.colno or
d.part4=c.colno or
d.part5=c.colno or
d.part6=c.colno or
d.part7=c.colno or
d.part8=c.colno or
d.part9=c.colno or
d.part1=c.colno or
d.part11=c.colno or
d.part12=c.colno or
d.part13=c.colno or
d.part14=c.colno or
d.part15=c.colno or
d.part16=c.colno)
and a.tabname not like "sys%"
and a.tabid > 100
order by 1,2
Thanks !
Damion Reeves
Informix Database Administrator
EDS Australia
Adelaide Solution Centre
"CONFIDENTIALITY NOTICE: This message originates from WHSmith USA Travel
Retail. This email message and all attachments may contain legally
privileged and confidential information intended solely for the use of the
addressee. If you are not the intended recipient, you should immediately
stop reading this message and delete it from the system. Any unauthorized
reading, distribution, copying, or other use of this message or its
attachments is strictly prohibited. All personal messages express solely the
sender's views and not those of WHSmith USA Travel Retail. This message may
not be copied or distributed without this disclaimer."