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.
