Teradata dbms structure and navigation, catalog commands?

Not applicable

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?

Just having a hard time navigating Teradata.

Junior Contributor

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

There's also HELP and SHOW (these commands are a bit hidden in the DDL manual).
"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.