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
