db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ollie2308 <ollie2...@yahoo.com>
Subject Re: List columns that make up an index
Date Fri, 20 Jan 2012 22:09:40 GMT

What root canal.... Just to get column names of an index.... 

thanks Rick.. I'll try that



Rick Hillegas-3 wrote:
> 
> 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
> 
> 

-- 
View this message in context: http://old.nabble.com/List-columns-that-make-up-an-index-tp33171994p33177395.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.


Mime
View raw message