Hi Robin.
You may access those informations thought database meta data.
Here is, as an attachment, a snippet of code to do that.
Basically, it calls getMetaData(), then getIndexInfo() to obtain info for all
indexes on a given table:
DatabaseMetaData dbmd = conn.getMetaData();
ResultSet rs = dbmd.getIndexInfo(null, "APP", "TBL", false, false);
The resulting ResultSet contains all the relevant informations. For your needs,
I think the most interesting columns will be INDEX_NAME, ORDINAL_POSITION and
COLUMN_NAME.
Hope this helps,
Sylvain
Robin Bale a écrit :
Hello All,
Somebody has given me a Derby database with some unknown indexes in. I can
work out the names of all the indexes on this database, but I would like to
know the names of the columns which make up the indexes.
Does anybody have any ideas?
Thanks in advance,
Robin Bale
--
Website: http://www.chicoree.fr
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
/**
* Demonstrate the use of DatabaseMetaData to retrieve index meta informations.
*
* The result is of the form:
* <pre>
* TABLE_CAT | TABLE_SCHEM | TABLE_NAME | NON_UNIQUE |
INDEX_QUALIFIER | INDEX_NAME | TYPE | ORDINAL_POSITION |
COLUMN_NAME | ASC_OR_DESC | CARDINALITY | PAGES |
FILTER_CONDITION |
* | APP | TBL | false |
| IDX2 | 3 | 1 |
C | A | null | null |
null |
* | APP | TBL | false |
|SQL091026130600360 | 3 | 1 |
V | A | null | null |
null |
* | APP | TBL | true |
| IDX1 | 3 | 1 |
I | A | null | null |
null |
* | APP | TBL | true |
| IDX1 | 3 | 2 |
C | D | null | null |
null |
* </pre>
* @author sylvain
*
*/
public class IndexMetaInformations {
public static void main(String args[]) throws SQLException {
Connection conn = DriverManager.getConnection(
"jdbc:derby:memory:indexMetaDataTestDB;create=true");
conn.setAutoCommit(true);
Statement stmt = conn.createStatement();
stmt.execute("CREATE TABLE TBL (I INT, C CHAR(80), V
VARCHAR(255), PRIMARY KEY(V))");
stmt.execute("CREATE INDEX IDX1 ON TBL (I ASC, C DESC)");
stmt.execute("CREATE UNIQUE INDEX IDX2 ON TBL (C ASC)");
DatabaseMetaData dbmd = conn.getMetaData();
ResultSet rs = dbmd.getIndexInfo(null, "APP", "TBL", false,
false);
ResultSetMetaData rsmd = rs.getMetaData();
for(int i = 0; i < rsmd.getColumnCount(); ++i) {
System.out.printf("%18s |", rsmd.getColumnLabel(i+1));
}
System.out.println();
while(rs.next()) {
for(int i = 0; i < rsmd.getColumnCount(); ++i) {
System.out.printf("%18s |", rs.getString(i+1));
}
System.out.println();
}
rs.close();
stmt.close();
conn.close();
}
}
