Archives of the TeradataForum

Message Posted: Thu, 29 Nov 2001 @ 16:03:50 GMT

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

The SHOW TABLE actually reconstructs the table definition from the contents of the data dictionary. This can be demonstrated by creating a table in mixing-up the case and then doing a SHOW TABLE. For example,

Col_01 InTeGeR FoRmAt '99' NoT NuLl

will appear in a SHOW TABLE as

Col_01 INTGER NOT NULL FORMAT '99'

The name of the table is contained in DBC.TVM: column TVMNNAME (tables are uniquely identified by either TVMID or TABLEID, depending on use). Also contained in DBC.TVM is whether the table is temporary (column TEMPFLAG), is fallback or not (column PROTECTIONTYPE), type of journalling (column JOURNALFLAG). There's more information in DBC.TVM, but I don't want to get carried away with detail.

The columns of your table are located in DBC.TVFIELDS (columns [or fields] are identified by FIELDID). The name of a column is contained in column FIELDNAME. The data type of your column is contained in FIELDTYPE. If a FORMAT clause has been specified, then it's located in FIELDFORMAT. Whether the column can be NULL'd is located in NULLABLE. The statistics for a column is contained in column FIELDSTATISTICS. You get the idea.

The index(es) of the table are contained in DBC.INDEXES (indexes are identified by INDEXNUMBER). The type of the index is contained in INDEXTYPE. Whether the index is unique is signaled by UNIQUEFLAG. The name of the index (if named) is contained in NAME. The statistics for the index is contained in column INDEXSTATISTICS. If an index consists of more than one column, then there's a separate row for each column that makes-up any given index (in which case, FIELDPOSITION indicates the column's position in the index - remember that column order actually doesn't matter in the Teradata world).

Most people use the VIEWs contained in DBC to access the data dictionary tables. Personally, I prefer accessing them directly - there's more information contained in the data dictionary than what the VIEWs will permit you to look at. However, remember that if you chose not to use the existing VIEWs, then you'll have to do all the associated work yourself: For example, you need to join DBC.TVM to DBC.DBASE to identify the database that contains your table.

Just a quick comment: If you do a HELP on DBC, you won't see all of the data dictionary tables - unless you're logged-on as user DBC. The VIEWs and MACROs will be listed. However if you can use the VIEWs in DBC, then you can do a SHOW TABLE or a SELECT against any of the data dictionary tables.

Just for convenience, here's a list of the data dictionary tables:

ACCESSRIGHTS
ACCLOGRULETBL
ACCLOGTBL
ACCOUNTS
ACCTG

COLLATIONTBL
CONSTRAINTNAMES

DATABASESPACE
DBASE
DBCASSOCIATION
DBCINFOTBL

ERRORMSGS
EVENTLOG

GLOBAL

HOSTS
HW_EVENT_LOG

INDEXES
INDEXNAME
INDOUBTRESLOG

LOGONRULETBL

MIGRATION

NEXT

OLDPASSWORDS
OWNERS

PARENTS

RCCONFIGURATION
RCEVENT
RCMEDIA
REFERENCEDTBLS
REFERENCINGTBLS
REPBATCHSTATUS
RESUSAGEICPU

RESUSAGEIPMA
RESUSAGEIVPR
RESUSAGESCPU
RESUSAGESCTL
RESUSAGESHST
RESUSAGESLDV
RESUSAGESOBJ
RESUSAGESPMA
RESUSAGESVPR
RESUSAGESVPR2

SESSIONTBL
SW_EVENT_LOG
SYSSECDEFAULTS

TABLECONSTRAINTS
TEMPSTATISTICS
TEMPTABLES

TEXTTBL
TRANSLATION
TRIGGERSTBL
TVFIELDS
TVM

UNRESOLVEDREFERENCES

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