在 Teradata 中检索列和其他元数据信息 [英] Retrieving column and other metadata information in Teradata

问题描述

我在 SQL Server 中有六个视图需要在 Teradata 中复制，但我无法找到 SQL 元数据表的 TD 等效项.我想复制以下功能(我认为这是不言自明的):

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

我还想知道是否有任何可用的 Teradata 在线参考资料；我遇到的一切似乎都是广告而不是实用信息.

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.

推荐答案

所有 Teradata 系统表都存储在 DBC 架构下.

All Teradata system tables are stored under DBC schema.

对于列，它是 dbc.columns

For columns, it is dbc.columns

select * from dbc.columns

对于视图，它是 dbc.tables，在名为 table_type 'V'(其中 V 代表视图)的列上有一个过滤器

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

我不确定如何获取 Teradata 中的所有功能.谁知道，请编辑此答案.

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

在 Teradata 中，DBC.Tables 包含系统中存在的许多对象.(例如存储过程、UDF、触发器、宏、视图、表、哈希索引、连接索引等)列表种类用于标识对象的类型.

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

这篇关于在 Teradata 中检索列和其他元数据信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！