db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Apache Wiki <wikidi...@apache.org>
Subject [Db-derby Wiki] Update of "ListKeyIndexes" by StanleyBradbury
Date Fri, 23 Jun 2006 21:23:48 GMT
Dear Wiki user,

You have subscribed to a wiki page or wiki category on "Db-derby Wiki" for change notification.

The following page has been changed by StanleyBradbury:
http://wiki.apache.org/db-derby/ListKeyIndexes

New page:
## Submitted June 23, 2006
== Listing Primary and Foreign Key Index names ==

The following query will return a list of the Primary and Foreign keys for each user table
in a Derby database along with the name of the backing index.  [Usage note: to cut and paste
this query into the Derby IJ tool you need to increase the IJ display width setting - the
following command did the trick for me:     maximumdisplaywidth 175; ]

{{{
select t.tablename, conglomeratename backIdxName, 
     cst.constraintname, cst.type 
from sys.systables t, sys.sysconstraints cst,
     sys.sysconglomerates cgl, sys.syskeys sk
where isindex = 'TRUE' 
     and cgl.tableid = t.tableid
     and (sk.constraintid = cst.constraintid 
          and cst.type='P'and sk.conglomerateid = cgl.conglomerateid) 
     and t.tableid = cst.tableid 
     and t.tabletype = 'T'
UNION
select t.tablename, conglomeratename backIdxName, 
     cst.constraintname, cst.type 
from sys.systables t, sys.sysconstraints cst,
     sys.sysconglomerates cgl, sys.sysforeignkeys fk
where isindex = 'TRUE' 
     and cgl.tableid = t.tableid
     and (fk.constraintid = cst.constraintid 
          and cst.type='F' and fk.conglomerateid = cgl.conglomerateid)
     and t.tableid = cst.tableid 
     and t.tabletype = 'T'
order by tablename, type
}}}

Mime
View raw message