determing index name

[Randy Letness <ra...@osafoundation.org>]

Is there a way to get a list of indexes on a table including the index 
name.  I tried using "show indexes", but it doesn't give the index name, 
just the columns that are indexed.  The use case is that i have an 
existing db and want to drop an index, but i don't know the name of the 
index.

-Randy

Re: determing index name

[Army <qo...@gmail.com>]

Bryan Pendleton wrote:
>> Is there a way to get a list of indexes on a table including the index 
>> name.  
> 
> You could also try looking in sys.sysconglomerates.
> 
>   SELECT * FROM SYS.SYSCONGLOMERATES
> 
> seems to show me the index name, but of course you'll have to do a bit
> of system catalog joining and querying to tie that back to your base table.

Or you can let dblook do the joining for you:

   java org.apache.derby.tools.dblook jdbc:derby:myDB -t myTable -verbose

That will list all of the DDL that relates to "myTable" in "myDB"--including the 
DDL for creating indexes on the table.  And yes, that includes index names :)

Army

Re: determing index name

[Bryan Pendleton <bp...@amberpoint.com>]

> Is there a way to get a list of indexes on a table including the index 
> name.  

This sounds like a nice enhancement request for 'show indexes'.

You could also try looking in sys.sysconglomerates.

   SELECT * FROM SYS.SYSCONGLOMERATES

seems to show me the index name, but of course you'll have to do a bit
of system catalog joining and querying to tie that back to your base table.

thanks,

bryan

Re: determing index name

[Stanley Bradbury <St...@gmail.com>]

Randy Letness wrote:
> Is there a way to get a list of indexes on a table including the index 
> name.  I tried using "show indexes", but it doesn't give the index 
> name, just the columns that are indexed.  The use case is that i have 
> an existing db and want to drop an index, but i don't know the name of 
> the index.
>
> -Randy
>
The Apache Wiki has a query that lists the primary and foreign key 
indexes.  You might be able to modify this for also include non-key indexes.

http://wiki.apache.org/db-derby/ListKeyIndexes