Can anyone provide a SQL solution by selecting from the sys tables ?
Sylvain Leroux wrote: > > 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(); > } > } > > -- View this message in context: http://old.nabble.com/Columns-in-index.-tp26056888p28293947.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
