CODEWITHSUNDEEP
javadatabasejdbc
praveen
praveen

Reputation: 331

get all indexes declared in database from java

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.

Upvotes: 2

Views: 1734

Answers (3)

Mike Q
Mike Q

Reputation: 23229

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.

Upvotes: 0

James Anderson
James Anderson

Reputation: 27478

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';

Upvotes: 1

PeterMmm
PeterMmm

Reputation: 24630

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.

Upvotes: 3

Related Questions