Indexes Schema Collection

Indexes schema collection returns information about INDEX objects by accessing the Advanced SQL Engine Data Dictionary views. It will access DBC.INDICES[V]X and DBC.COLUMNS[V]X if UseXViews is set to true; otherwise it will access DBC.INDICES[V] and DBC.COLUMNS[V] Data Dictionary view.

Column Name	Data Type	Description
CATALOG_NAME	String	This field is always set to EMPTY.
TABLE_SCHEMA	String	Returns the name of the DATABASE in which TABLE object resides.
TABLE_NAME	String	Returns the name of the TABLE object.
INDEX_SCHEMA	String	Returns the name of the DATABASE in which INDEX object resides.
INDEX_NAME	String	Returns the name assigned to the Index. This field is NULL if the index does not have a name.
PRIMARY_KEY	Int32	Returns: 0 indicating that the Index is not a Primary Key. -1 indicating that the Index is a Primary Key.
UNIQUE	Int32	Returns: 0 indicating that the Index is not unique (e.g. Primary Index) -1 indicating that the Index is unique (e.g. Unique Primary Index)
TYPE	Int32	This field is always set to 2.
ORDINAL_POSITION	Int16	Returns the position of the column in the Index. For a composite Index, this indicates the order of the columns making up the Index.
COLUMN_NAME	String	Returns the name of the Column in the Index.
COLLATION	Int32	This field is always set to 1.
INDEX_NUMBER	Int16	Returns an internal number assigned to the index.
INDEX_TYPE	String	Returns the type of Index as: Derived Column Partition Statistics Hash Index Hash Ordered ALL Covering Secondary Internal Use Index Type Join Index Multi-Column Statistics Nonpartitioned Primary Ordering Column of a Composite Secondary Index Partitioned Primary Primary Key Secondary Unique Constraint Value Ordered ALL Covering Secondary Value Ordered Secondary Primary Amp
IS_NULLABLE	String	Returns a code to indicate whether or not the column may have a null value: Y for yes it may have a null value. N for no it may not have a null value.
VALIDTIME_CONSTRAINT	String	Returns a constraint type for a Validtime Temporal constraint. This property is only applicable for a Temporal column. The valid constraint types: C Current. S Sequenced. N Non-sequenced. NULL No constraint or ValidTime not supported.
TRANSACTIONTIME_CONSTRAINT	String	Returns a constraint type for a TransactionTime Temporal constraint. This property is only applicable for a Temporal column. The valid constraint types: C Current. S Sequenced. N Non-sequenced. NULL No constraint or TransactionTime not supported.
TEMPORAL_UNIQUE_OR_PK	String	Returns a Unique or Primary Key constraint type associated with a time dimension. This property is only applicable for a Temporal column. The valid constraint types: U Unique. P Primary Key. NULL No unique or primary key constraint associated with a time dimension.

Indexes Schema Collection can be filtered or restricted to:

Restriction Column Name	Restriction Number	Description
TABLE_SCHEMA	1	This restriction can be set to a valid DATABASE object name.
TABLE_NAME	2	This restriction can be set to a valid TABLE object name.
INDEX_NAME	3	This restriction can be set to a valid Index Name.

Indexes schema collection rows are ordered by:

Column Name	Description
TABLE_SCHEMA	Ascending case sensitive sort order. For example A through Z followed by a through z.
TABLE_NAME	Ascending case sensitive sort order.
INDEX_NUMBER	Ascending sort order.
ORDINAL_POSITION	Ascending sort order.