Teradata data dictionary tables are metadata tables present in the DBC database. It can be used for variety of things such as checking table size, query bottleneck and database size etc.
SELECT USER;
Output: TUTORIAL_USER
SELECT DATABASE;
Output: TUTORIAL_DB
SELECT SESSION;
Output: 2342424
SELECT databasename,tablename,tablekind,version,journalflag,creatorname,createtimestamp,lastaltertimestamp
FROM dbc.tables
WHERE databasename = ['databasename']
AND tablename = ['objectname']
AND TableKind = 'T' ;
Table Kind | Object Type |
T | Table |
V | View |
M | Macro |
P | Stored Procedure |
G | Trigger |
I | Join Index |
N | Hash Index |
SELECT databasename,tablename,columnname,columnformat,columntitle,columnlength,columntype,defaultvalue
FROM dbc.columns
WHERE databasename = ['databasename']
AND tablename = ['objectname']
ORDER BY columnname;
SELECT databasename,tablename,indexnumber,indexname,columnname,indextype,uniqueflag,indexmode,accesscount
FROM dbc.indices
WHERE databasename = ['databasename']
AND tablename = ['objectname']
ORDER BY indexnumber;
SELECT databasename, tablename, indexname, indexnumber, constrainttype, creatorname, createtimestamp, constrainttext
FROM dbc.indexconstraints
WHERE databasename = ['databasename']
AND tablename = ['objectname'] ;
SELECT databasename,tablename,CAST(SUM(currentperm)/(1024*1024*1024) AS DECIMAL(18,5)) AS TableSize_in_GB
FROM dbc.allspace
WHERE databasename = ['databasename']
AND tablename = ['objectname']
GROUP BY databasename,tablename;
SELECT databasename,tablename, CAST(SUM(currentperm)/(1024*1024*1024) AS DECIMAL(18,5)) AS TableSize_in_GB
FROM dbc.tablesize
WHERE databasename = '[databasename]'
AND tablename = '[objectname]'
GROUP BY databasename,tablename ;
SELECT databasename, CAST(SUM(maxperm)/(1024*1024*1024) AS DECIMAL(18,5)) AS "Allocated(GB)",
CAST(SUM(currentperm)/(1024*1024*1024) AS DECIMAL(18,5)) AS "Used(GB)",
"Allocated(GB)"- "Used(GB)" AS "Free(GB)"
FROM dbc.diskspace WHERE databasename = ['databasename']
GROUP BY 1;
SELECT SUM(cnt) as total_rows,
COUNT(*) as total_amps,
MAX(cnt) as Max_rows_per_amp,
MIN(cnt) as min_rows_per_amp,
AVG(cnt) as avg_rows_per_amp
FROM (SELECT HASHAMP(HASHBUCKET(HASHROW([PI]))), COUNT(*) FROM ['Tablename'] Group by 1 ) dt (a, cnt) ;
SELECT username,sessionno,defaultdatabase,ifpno,partition,logicalhostid,hostno, logondate,logontime,logonsource
FROM dbc.sessioninfo
WHERE username = '[databaseuser]' ORDER BY 2 ;
SELECT databasename,dbkind,creatorname,ownername,permspace,spoolspace,tempspace,commentstring,
createtimestamp,lastaltername,lastaltertimestamp
FROM dbc.databases ;
SELECT errortext FROM dbc.errormsgs WHERE errorcode = [errorCodeNumber];
SELECT errortext FROM dbc.partitioningconstraintsv WHERE databasename='tutorial_db' AND tablename='employee';
Number of Nodes
SELECT COUNT(DISTINCT nodeid) FROM dbc.resusagescpu;
Number of Amps on each Node
SELECT nodeid,COUNT(DISTINCT vproc) number_of_amps
FROM dbc.ResCpuUsageByAmpView
GROUP BY nodeid;
Number of AMPs in the system
SELECT HASHAMP()+1;
SELECT databasename, subjecttabledatabasename, tablename, triggername, enabledflag, actiontime, event,
triggercomment,creatorname, createtimestamp, lastaltername, lastaltertimestamp
FROM dbc.triggers
WHERE databasename = ['databasename'];
SELECT databasename, tablename
FROM dbc.allrolerights
WHERE rolename = ['Role_name']
AND tablename= ['tablename/viewname']
GROUP BY 1,2
Order by 1,2;
SELECT username, databasename, tablename, columnname, accessright, grantauthority, grantorname,
allnessflag, creatorname, createtimestamp
FROM dbc.allrights
WHERE databasename = ['databasename']
AND tablename = ['tablename/viewname']
ORDER BY 1;
SELECT databasename,tablename,accessright,grantauthority,grantorname,creatorname,createtimestamp
FROM dbc.userrights
WHERE databasename =['databasename']
AND tablename =['tablename/viewname']
ORDER BY accessright;
SELECT infokey, CAST(infodata AS VARCHAR(50)) FROM dbc.dbcinfo;
Output:
InfoKey InfoData
===================== ===================
VERSION 16.10.00.04
LANGUAGE SUPPORT MODE Standard
RELEASE 16.10.00.04
Table Size / Count(rows)