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
SQL Statements with Equals VS In
How to Change the Primary Key to Be Non-Clustered
How to Find Values in All Caps in SQL Server
Left Inner Join VS. Left Outer Join - Why Does the Outer Take Longer
Insert Record to SQL Table with Identity Column
How to Make Comment Reply Query in MySQL
Using Bcp Utility to Export SQL Queries to a Text File
T:Sql: Select Values from Rows as Columns
Convert Rows to Columns Using 'Pivot' in Mssql When Columns Are String Data Type
How to Expand a "Condensed" Postgresql Row into Separate Columns
How to Match Multiple Column in a Table with SQLite Fts3
SQL Group by Day, Show Orders for Each Day
Filter Duplicate Rows Based on a Field
How to Speed Up This Indexed View