INDEXCOLUMNS

Prerequisites

You have generated the demo data for the SQL Tutorial.

Log on to the demo database instance DEMODB as user MONA.

Create indexes. Proceed as described in SQL Tutorial, Indexes.

Examples

You can use the system table INDEXCOLUMNS to determine the following database information, among other things:

?     All indexed columns of the table CUSTOMER

SELECT DISTINCT columnname
  FROM DOMAIN.INDEXCOLUMNS
    WHERE tablename = 'CUSTOMER'

?     All inversions for the table CUSTOMER, sorted according to the name of the index and there according to the sequence of columns as specified in the index definition

SELECT indexname, type, columnname, sort
  FROM DOMAIN.INDEXCOLUMNS
    WHERE tablename = 'CUSTOMER' ORDER BY indexname, columnno

?     Information about the columns that comprise the ADDRESS_INDEX of the table CUSTOMER

SELECT columnname, sort, datatype, len
  FROM DOMAIN.INDEXCOLUMNS
    WHERE tablename = 'CUSTOMER'
      AND indexname = 'ADDRESS_INDEX' ORDER BY columnno

All indexes: see INDEXES