Archives of the TeradataForum

Message Posted: Mon, 18 Aug 2003 @ 14:07:40 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

The previous proposal would provide the basic index information but would not tell you if the indexes were Primary or Secondary, or whether they were Unique.

The following will give you a better description:

SELECT IndexNumber,ColumnName,ColumnPosition,IndexType,UniqueFlag
FROM dbc.Indices
WHERE DataBaseName=? AND TableName=?
AND IndexType IN ('P','S',K','U')
ORDER BY 1, 3

The above assumes that you are only looking for Primary/Secondary indexes rather than Join / Hash indexes also. To add these you would need to look for IndexType 'J' and 'N' also.

(If using V2R5 then look for 'Q' also - but this will not give you the partitioning details of the partitioned index.)

The following case statement can be used to interpret the index types:

CASE IndexType
WHEN 'P' THEN 'Primary'
WHEN 'S' THEN 'Secondary'
WHEN 'K' THEN 'Primary Key'
WHEN 'U' THEN 'Unique Constraint'
WHEN 'Q' THEN 'Partitioned'
WHEN 'V' THEN 'Value Ordered'
WHEN 'J' THEN 'Join Index'
WHEN 'N' THEN 'Hash Index'
WHEN 'O' THEN 'Value Order (All)'
WHEN 'H' THEN 'Secondary (All)'
ELSE IndexType END

Mike Dempsey
Teradata Client Tools

	<Prev	Next>		<<First	<Prev	Next>	Last>>