Teradata data dictionary tables are metadata tables in the DBC database. It can be used for variety of things.

Teradata Data Dictionay(Metadata) Queries

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.

• To view current User database,Default database and Session Id

  
  SELECT USER;
  Output: TUTORIAL_USER
  
  SELECT DATABASE;
  Output: TUTORIAL_DB
  
  SELECT SESSION;
  Output:  2342424
  

  
  
• Dbc.Tables : Objects present in a database and their related information

  
  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

  
  
  
• Dbc.Columns : Column informatiom of tables, views, join index & hash index etc.

  
  SELECT databasename,tablename,columnname,columnformat,columntitle,columnlength,columntype,defaultvalue
  FROM dbc.columns 
  WHERE databasename = ['databasename'] 
  AND tablename      = ['objectname'] 
  ORDER BY columnname;
  

  
  
• Dbc.Indices : Stores all the index related informatiom for tables, views, join index , hash index & secondary index etc.

  
  SELECT databasename,tablename,indexnumber,indexname,columnname,indextype,uniqueflag,indexmode,accesscount
  FROM dbc.indices 
  WHERE databasename = ['databasename']
  AND   tablename    = ['objectname'] 
  ORDER BY indexnumber;
  

  
  
• Dbc.Indexconstraints : Stores all the index's constraints specified while creating indexes.

  
  SELECT databasename, tablename, indexname, indexnumber, constrainttype, creatorname, createtimestamp, constrainttext
  FROM dbc.indexconstraints 
  WHERE databasename = ['databasename']
  AND   tablename    = ['objectname'] ;
  

  
  
• Table Size : Table size can be determined from multiple tables for example : Dbc.Allspace & Dbc.Tablesize.

  
  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 ;
  

  
  
• Database Size : Database size can determined using Dbc.Diskspace.

  
  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;
  

  
  
• Table Skewness : To determined table's skewness .

  
  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) ; 
  
  

  
  
• Dbc.Sessioninfo : Active sessions and related information.

  
  SELECT username,sessionno,defaultdatabase,ifpno,partition,logicalhostid,hostno, logondate,logontime,logonsource
  FROM  dbc.sessioninfo
  WHERE username = '[databaseuser]' ORDER BY 2 ;
  

  
  
• Dbc.Databases : All database present within system and its proprties (more detail can be available in Dbc.DBase .

  
  SELECT databasename,dbkind,creatorname,ownername,permspace,spoolspace,tempspace,commentstring,
         createtimestamp,lastaltername,lastaltertimestamp
  FROM dbc.databases ;
  

  
  
• Dbc.Errormsgs : To error message for an error code.

  
  SELECT errortext FROM dbc.errormsgs WHERE errorcode = [errorCodeNumber];
  

  
  
• Dbc.PartitioningConstraintsV : To get detail about PPI table's partitions and constraint.

  
  SELECT errortext FROM dbc.partitioningconstraintsv WHERE databasename='tutorial_db' AND tablename='employee';
  

  
  
• Number of Nodes/AMPs in Teradata System:

  
  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;
  
  

  
  
• Dbc.Triggers : Triggers and related information.

  
  SELECT databasename, subjecttabledatabasename, tablename, triggername, enabledflag, actiontime, event, 
         triggercomment,creatorname, createtimestamp, lastaltername, lastaltertimestamp
  FROM dbc.triggers 
  WHERE databasename = ['databasename'];
  

  
  
• Dbc.Allrolerights : Check Table/view access for a particular role.

  
  SELECT databasename, tablename
  FROM dbc.allrolerights 
  WHERE  rolename = ['Role_name']
  AND    tablename= ['tablename/viewname']
  GROUP BY 1,2
  Order by 1,2;
  

  
  
• Dbc.Allrights : Access right granted on a given object.

  
  SELECT username, databasename, tablename, columnname, accessright, grantauthority, grantorname, 
       allnessflag, creatorname, createtimestamp 
  FROM dbc.allrights
  WHERE databasename = ['databasename']
  AND   tablename    = ['tablename/viewname']
  ORDER BY 1;
  

  
  
• Dbc.Userrights : Access rights that the used has on a given table

  
  
  SELECT databasename,tablename,accessright,grantauthority,grantorname,creatorname,createtimestamp
  FROM dbc.userrights 
  WHERE databasename  =['databasename']
  AND tablename       =['tablename/viewname']
  ORDER BY accessright;
  

  
  
• Teradata Version:

  
  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
  

  
  
• Average Row Size: The easiest way to get an average row size for a populated table is always size of the table divided by number of records in the table(count).

  
  Table Size / Count(rows)