Retrieving Column and Other Metadata Information in Teradata

Retrieving column and other metadata information in Teradata

All Teradata system tables are stored under DBC schema.

For columns, it is dbc.columns

select * from dbc.columns

For views, it is dbc.tables with a filter on a column something named table_type 'V' (where V stands for Views)

select * from dbc.tables

I am not sure about how to get all functions in Teradata. Whoever knows it, please edit this answer.

In Teradata DBC.Tables contains many of the objects that exist on the system. (e.g. Stored Procedures, UDF, Triggers, Macros, Views, Tables, Hash Index, Join Index, etc.) The column Table Kind is used to identify the type of object.

SELECT *
FROM DBC.TABLES
WHERE TABLEKIND = '<see below>'

A = Aggregate Function
B = Combined Aggregate Function and ordered analytical function
D = JAR
E = External Stored Procedure
F = Standard Function
G = Trigger
H = Instance or Constructor Method
I = Join Index
J = Journal
M = Macro
N = Hash Index
O = No Primary Index (Table)
P = Stored Procedure
Q = Queue Table
R = Table Function
S = Ordered Analytical Function
T = Table
U = User-defined data type
V = View
X = Authorization
Y = GLOP Set

How can I query system information and metadata?

-- 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.

How to find all the tables in database Teradata with specific column names in them?

You are looking for this:

SELECT tablename
FROM dbc.columnsV
WHERE ColumnName in ('col1', 'col2')

Show column name and data type in Teradata?

You can use "SHOW VIEW VIEW_NAME" or "HELP COLUMN VIEW_NAME.*" to get all column names and datatype in the view.

Example

HELP COLUMN dbc.Allspace.*

OUTPUT

Column Name     Type    Nullable    Format                          Max Length  
Vproc I2 N -(5)9 2
DatabaseName CF Y X(30) 30
AccountName CF Y X(30) 30
TableName CF Y X(30) 30
MaxPerm F N ---,---,---,---,--9 8
MaxSpool F N ---,---,---,---,--9 8
MaxTemp F N ---,---,---,---,--9 8
CurrentPerm F N ---,---,---,---,--9 8
CurrentSpool F N ---,---,---,---,--9 8
CurrentTemp F N ---,---,---,---,--9 8
PeakPerm F N ---,---,---,---,--9 8
PeakSpool F N ---,---,---,---,--9 8
PeakTemp F N ---,---,---,---,--9 8
MaxProfileSpool F Y ---,---,---,---,--9 8
MaxProfileTemp F Y ---,---,---,---,--9 8

Columntype of a column in a view

This is the only way to get detailed metadata for views columns:

help column databasename.tablename.*;


Related Topics



Leave a reply



Submit