CODEWITHSUNDEEP
teradata
Adam
Adam

Reputation: 2455

How can I query system information and metadata?

In the datawarehouse which is build on Teradata how can I find out how many Databases exist in the whole datawarehouse, how many data marts exist in the warehouse, which databases have the most tables, which databases are most frequently used. This is certainly a programming question, because I am asking how to query the Datawarehouse to get the desired informations.

I would like to get a look and feel about the datawarehouese. Similar informations or suggestions would certainly help - what should I keep an eye on? What is the "heart" ot the Data warehouse. What is the first thing you need to look when you start to work with complete new Datawarehouse?

Upvotes: 0

Views: 2856

Answers (2)

ChrisCamp
ChrisCamp

Reputation: 682

-- how many databases exist 




SEL COUNT(*) 
FROM dbc.databases 
WHERE dbkind = 'D'

-- which databases have the most tables?



SEL databasename, COUNT(*) 
FROM dbc.tables
WHERE tablekind = 'T' GROUP BY 1 ORDER BY 2 DESC

TABLEKIND definitions
  • A: aggregate UDF
  • B: COMBINED AGGREGATE AND ORDERED ANALYTICAL FUNCTION
  • E: EXTERNAL STORED PROCEDURE
  • F: SCALAR UDF
  • G: TRIGGER
  • H: INSTANCE OR CONSTRUCTOR METHOD
  • I: JOIN INDEX
  • J: JOURNAL
  • M: MACRO
  • N: HASH INDEX
  • P: STORED PROCEDURE
  • Q: QUEUE TABLE
  • R: TABLE FUNCTION
  • S: ORDERED ANALYTICAL FUNCTION
  • T: TABLE
  • U: USER-DEFINED DATA TYPE
  • V: VIEW
  • X: AUTHORIZATION

-- which databases are most frequently used. 

SEL DatabaseName, AccessCount, LastAccessTimeStamp 
FROM dbc.databases ORDER BY AccessCount

Also be sure to check out the dbc.columns table for information on what columns are in each table, their datatypes, etc.

Upvotes: 3

BellevueBob
BellevueBob

Reputation: 9618

Go to the Teradata Documentation web site and find the "Data Dictionary" book for the version of Teradata you are using. There are numerous dictionary views available.

The one in particular that includes all databases in the environment is called "dbc.databases", so run this:

select *
from   dbc.databases
where  DBKind = 'D'

The other value for DBKind is 'U', which would include users on the system.

Information about tables is in dbc.tables and other views. I'm not aware of any Teradata concept of "data mart" so I can't help you there.

Answering a question like "most frequently used" would require using one of the query log tables (DBQL). However, you should ask your system DBA if these views are available to you.

Upvotes: 4

Related Questions