Archives of the TeradataForum

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

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

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
     dbase.databaseid=tvm.databaseid
     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;

-dave.clark

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