Tables and indexes

Question: Is it possible to use SQL to find out if an index has been defined?

Answer

It is possible by searching data dictionary tables; see the examples below.

The definition SQL generation facility of the dictionary import/export utility (pdexp command) can also be used for this purpose.

Example 1

Search for tables that have indexes:

SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_ID
    FROM MASTER.SQL_TABLES WHERE N_INDEX >= 1

Example 2

Display the index names and columns names for a defined table:

SELECT TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,INDEX_ID,COLUMN_NAME
    FROM MASTER.SQL_INDEX_COLINF
        WHERE TABLE_SCHEMA = USER

Example 3

Display the table name, table ID, index name, index ID, and column names for a table that has an index:

SELECT X.TABLE_SCHEMA,X.TABLE_NAME,X.TABLE_ID,
  INDEX_ID,COLUMN_NAME
    FROM MASTER.SQL_TABLES X,MASTER.SQL_INDEX_COLINF Y
        WHERE X.TABLE_SCHEMA = Y.TABLE_SCHEMA
             AND X.TABLE_SCHEMA = USER
             AND X.TABLE_NAME = Y.TABLE_NAME
             AND N_INDEX >= 1

Appendix A.5 Tables and indexes

(1) How can I obtain an existing index's definition?

(2) How can I add columns to an existing FIX table?