get all indexes declared in database from java

Question

Hi All, Is there any standard java api that returns the indexes declared in the database. I tried using the getIndexInfo() in database meta data but that seems to expect a table name as input and does not meet my requirements. Thx.

Answer 1

Indexes are declared on tables. So you should first retrieve all tables with DatabaseMetaData.getTables() and then loop over the table names to get all indexes.

Answer 2

No, you need to fire off some sql which will vary depending on the DBMS you are using.

For example DB2 would be:-

select * from sysibm.sysindexes where tbcreator = 'IMPACT';

For sqlite it would be:-

Select * from sqlite_master where type = 'index';

Answer 3

There is no 100% portable "query" way of doing this, however many DBs do implement the standard INFORMATION_SCHEMA, so you can queries like this.

    sql = "select TABLE_NAME, INDEX_NAME, NON_UNIQUE, COLUMN_NAME " +
            "from INFORMATION_SCHEMA.STATISTICS " +
            "where TABLE_SCHEMA = ? " +
            "order by TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX";

MySQL and SQLServer support this. Oracle does not.

See this page

EDIT: I originally said "no 100% portable way", however you can use the JDBC metadata APIs which will achieve this, however as noted in a previous answer this may be inefficient depending on the number of tables.

Answer 4

While not a "standard" API, take a look at SchemaCrawler, which makes it easy for Java programmers to obtain database metadata. All database metadata (including indexes) are returned as plain-old Java objects.