Columns in index.

[Robin Bale]

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


--
Emnico Developing EmNOC for Managing IT Networks - www.emnico.com

[Sylvain Leroux]

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();
        }
}