[Db-derby Wiki] Update of "ListKeyIndexes" by StanleyBradbury

[Apache Wiki <wi...@apache.org>]

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
}}}