Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Wed, 03 Mar 2005 @ 00:30:36 GMT

  <Prev Next>   <<First <Prev

Subj:   Re: DBC.INDEXES and Index Types
From:   Clark, Dave

To expand on what Dieter said....

How do you list the existing database elements?

Within DBC.TVM, the field TABLEKIND has the following options:

     o) T = Table
     o) V = View
     o) M = Macro
     o) J = Journal Table
     o) I = Join Index Table
     o) P = Stored Procedure
     o) G = Trigger
     o) F = Scalar UDF
     o) A = Aggregate UDF
     o) N = Hash Index Table

Within DBC.INDEXES, the field INDEXTYPES has the following options:

     J--> Join index
     K--> Primary Key
     N--> Hash Index
     P--> Non Partitioned Primary
     Q--> Partitioned Primary
     S--> Secondary
     V--> value ordered
     U--> Unique Constraint
     H--> Hash ordered All covering secondary
     O--> Value ordered all covering secondary
     I--> ordering column of a composite secondary index
     1--> field1 column of a join or hash index
     2--> field2 column of a join or hash index

These can then be used to identify the various element definitions within Teradata. For example, to check for a Join Index, which is both a table and an index:

     /* check TVM */

     sel dbase.databasename (FORMAT 'X(15)'),
     tvm.tvmname (FORMAT 'X(25)')
     from dbc.tvm,dbc.dbase where
     and tvm.tablekind='I'
     order by 1,2;

     /* and INDEXES */

     SEL DISTINCT dbase.databasename (FORMAT 'X(15)'),
     tvm.tvmnameI (FORMAT 'X(25)'),
     indexes.name (FORMAT 'X(15)'),
     indexes.IndexNumber (FORMAT 'zzz9')
     FROM dbc.indexes,dbc.dbase,dbc.tvm
     WHERE     dbase.databaseid=indexes.databaseid
           and tvm.tvmid=indexes.tableid
           and indexes.indextype in ('J', '1', '2')
     ORDER BY 1,2;


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