db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: List columns that make up an index
Date Fri, 20 Jan 2012 16:18:55 GMT
On 1/19/12 6:49 PM, ollie2308 wrote:
> Hi
>
> I am having trouble writing a query that uses the derby system tables to
> return all  index names and the columns and their order that make up the
> indexes.
>
> Any help would be appreciated.
>
> Thanks
>
>
As Kristian noted, that information is hard to get at. To help with this 
problem, I have attached a table function to DERBY-5577. The table 
function exposes the column information which is hidden inside the 
SYSCONGLOMERATES.DESCRIPTOR column. To use the table function, you must 
compile the class and then register it with the database:

create function indexColumns()
returns table
(
     conglomerateid char( 36 ),
     isUnique boolean,
     isUniqueWithDuplicateNulls boolean,
     positionInIndex int,
     baseColumnPosition int,
     isColumnAscending boolean
)
language java parameter style derby_jdbc_result_set reads sql data
external name 'IndexColumnVTI.indexColumns';

After that,  you can join the table function with other Derby metadata. 
Here is a query which lists out all of the indexes along with their 
columns and the positions of those columns in the indexes:

select t.tableName, cong.conglomerateName indexName, col.columnName, 
ic.positionInIndex
from sys.systables t, sys.sysconglomerates cong, sys.syscolumns col, 
table( indexColumns() ) ic
where t.tableID = cong.tableID
and t.tableID = col.referenceID
and cong.conglomerateID = ic.conglomerateID
and ic.baseColumnPosition = col.columnNumber
order by t.tableName, cong.conglomerateName, ic.positionInIndex;

Hope this helps,
-Rick

Mime
View raw message