Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Wed, 15 Apr 2009 @ 15:22:48 GMT

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

Subj:   Re: Uniquely identify indexes
From:   Anomy Anom

<-- Anonymously Posted: Wednesday, April 15, 2009 11:04 -->

Depends on what you mean. DBC.Indices has a row for each index column, so if you are looking for unique (DatabaseName, TableName, IndexNumber) you could simply qualify on ColumnPosition=1.

You'd probably also need to filter on IndexType. Not all "index types" are actually "indexes", e.g. 'M' for Multi-Column, non-Index stats; the list of valid values depends on your release. See the Data Dictionary manual.

Note that in Teradata, indexes need not have names; if you omit a name when you create the index, name will be NULL, not a system-generated value. And if an index does have a name, the scope is only for that table, i.e. you can use exactly the same index name for two entirely different tables. So if you want to do something with this list like generate COLLECT STATS statements, you may need to do a recursive query or multiple self-joins to string together the list of column names that define the index.

  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016