Retrieving column and other metadata information in Teradata
In Teradata system tables are stored under DBC schema.
1- For columns, it is dbc.columns
select * from dbc.columns
eg: 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
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
Realtime Example:-
SELECT a.TBL_REC_ID ,b.DOMAIN_NM ,b.TGT_DB_NM ,b.SRC_TBL_NM ,b.TGT_TBL_NM ,a.COL_REC_ID ,a.SRC_TBL_COL_NM ,a.TGT_TBL_COL_NM ,a.PUB_VW_COL_NM ,a.SRC_COL_DATA_TYP ,a.TGT_COL_DATA_TYP ,a.COL_LEN ,a.COL_SCALE ,a.COL_FORMAT ,a.COL_NOT_NULL_IND ,a.FILT_CRI ,a.AS_OF_TMS ,a.UPD_TMS ,a.AS_AT_TMS ,a.SRC_DEL_IND ,a.JN_IND
FROM DW_PUBLISH.EDW_META_COL a INNER JOIN DW_PUBLISH.EDW_META_TBL b ON a.TBL_REC_ID = B.TBL_REC_ID --WHERE a.JN_IND = 'Y' ------ Here if you remove this Filter, you will all the Land to Core Column mapping ---- --WHERE b.TGT_TBL_NM = 'Pharm_claim_src' --WHERE a.TGT_COL_DATA_TYP = 'BIGINT' WHERE b.domain_nm LIKE '%PHARM%' ---WHERE b.DOMAIN_NM NOT IN ('EZCAP_AUDIT','AUTHORIZATIONS', 'GUIDING_CARE','CARE_MGMT', 'AUTHORIZATION','Staff_Productivity' ) ORDER BY a.TBL_REC_ID, a.COL_REC_ID