Archives of the TeradataForum
Message Posted: Thu, 29 Nov 2001 @ 16:03:50 GMT
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:
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|