Jirka's Public Notepad

Data Engineering | Python | SQL Server | Teradata

October 11, 2015 By Jiří Hubáček Leave a Comment

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

Related

Filed Under: Uncategorized

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  • GitHub
  • LinkedIn
  • RSS
  • Twitter
© 2022 · Jiří Hubáček, PGP