10

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.

Raj
  • 22,346
  • 14
  • 99
  • 142
William
  • 508
  • 3
  • 10
  • 18
  • Follow-up: dbc.columns (as emailenin recommended) seems to give me everything I need for the first view except for ordinal position. Does TD save that information? – William Oct 21 '11 at 18:35
  • 1
    COLUMN_ID in DBC.Columns can be used to ordinal postion for a column in the table or view. It typically seeds at 1025 and increments from there. – Rob Paller Oct 21 '11 at 18:48
  • You'll find all Teradata manuals at info.teradata.com, simply choose your version. The best choice is the full documantation CD including Full Search. – dnoeth Nov 13 '13 at 13:44

1 Answers1

18

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
BenMorel
  • 34,448
  • 50
  • 182
  • 322
Raj
  • 22,346
  • 14
  • 99
  • 142
  • 1
    Added details on TableKind for finding other objects in a Teradata Database. – Rob Paller Oct 21 '11 at 18:49
  • 3
    Just a remark: use `dbc.tablesV` instead of `dbc.tables`. The non-V views are deprecated since TD12, the emulate the old data dictinary with object names in CHAR(30) LATIN. Everything is VARCHAR(128) UNICODE now and TD14.10 finally allows 128 character object names. – dnoeth Nov 13 '13 at 13:39