Teradata dbms structure and navigation, catalog commands?
I'm trying to locate objects within Teradata (database, tables, views, etc). How do I find something if I don't know where to look? For example, I went to Teradata Administrator and tried to find a database. But there are hundreds of databases in that nested tree structure. How do I find what I'm looking for?
Is there a reference with catalog commands listed? I need to "find database" or "find columname", etc, but I don't easily find the commands, nor the metadata names (column or columname? tablename, table_name, name, etc?).
And is there a reference that will show me about the dbms structure (is dbc a root node? are databases nested as they appear in Teradata SQL Administrator, and do I have to qualify them down several levels?
Re: Teradata dbms structure and navigation, catalog commands?
Hi Steve, if you know the database name there's a "Find" in the "Edit" menu.
dbc is the root for all databases/users. As database/user names are unique you don't have to know the "path" to a database (unless you want to locate it manually in TD Amin :-). A DATABASE in Teradata is similar to a SCHEMA in Standard SQL, in fact there's only one "database" (=your Teradada system) with lots of schemas (=databases/users).
The hierarchy of databases/users is used for: 1. backup, you can archive a full branch of the hierarchy with a single statement. 2. access rights management, an owner of a database can grant/revoke any rights to/from any user. 3. inherited access rights, these are usually replace by ROLEs since V2R5.
dbc is also owner of the metadata, all system tables/views are stored within dbc.
There's a "Data Dictionary" manual with details about Teradata metadata, e.g. dbc.databasesV: all databases and users dbc.tablesV: all tables/views/macros/SPs etc in all databases dbc.columnsV dbc.indexesV
There's also HELP and SHOW (these commands are a bit hidden in the DDL manual). e.g. "HELP DATABASE xy" is similar to a query on dbc.tables with a "WHERE databasename = 'xy'"
"SHOW TABLE xy" returns the current source code of that table.