List table’s indexes in Teradata

Getting all table’s indexes in Teradata Studio is not quite a straightforward process. Therefore it’s always handy to have a query to DBC database available:

SELECT ind.*
FROM dbc.indices ind
WHERE ind.tablename = 'CUSTOMER_TB'
AND ind.databasename = 'SALES_DB'
;

indextype column represents the index’s type. As some values’ meaning might not be obvious, refer to the following table:

indextype	description
P	Nonpartitioned Primary Index
Q	Partitioned Primary Index
S	Secondary Index
J	Join Index
N	Hash Index
K	Primary Key
U	Unique Constraint
V	VONUSI – Value Ordered Nonunique Secondary Index
H	Hash Ordered ALL Covering Secondary Index
O	Value Ordered ALL Covering Secondary Index
I	Ordering Column of a Composite Secondary Index
1	Field 1 Column of a Join/Hash Index
N	Field N Column of a Join/Hash Index