Re: Columns in index.

[ollie2308 <ol...@yahoo.com>]

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.

Re: Columns in index.

[Bryan Pendleton <bp...@gmail.com>]

>>> 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.

Open the database using ij and use the 'describe' and 'show indexes' commands.

thanks,

bryan