Retrieving column and other metadata information in Teradata
I have a half dozen views in SQL Server that I need to replicate in Teradata, but I haven't been able to find the TD equivalent of the SQL metadata tables. I'd like to replicate the following functionality (which I assume is fairly self-explainatory):
select table_name, column_id ordinal_position, column_name,
data_type, char_length char_max_length,
data_precision numeric_precision, data_scale numeric_scale
from user_tab_columns
select name as FUNCTION_NAME
from sys.objects
where type_desc='SQL_SCALAR_FUNCTION'
select TABLE_NAME as VIEW_NAME
from INFORMATION_SCHEMA.VIEWS
I'd also like to know if there are any usable Teradata references online; everything I run across seems to be advertising rather than practical information.
Answer
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