Teradata system tables are metadata tables present in the DBC database.

Teradata System Tables Queries Part 2

Teradata system tables are metadata tables present in the DBC database.

• Teradata Version
• Find Logged in Teradata server
• List of all databases/users in Teradata
• Table Size
• Average Row Size
• Teradata Error Messages
• Identify All Roles assigned to Teradata Table
• Identify All rights for a Teradata user
• Teradata Access Right Abbreviations

• Teradata Version:

  
  SELECT infokey, CAST(infodata AS VARCHAR(50)) FROM dbc.dbcinfo;
  
  Output: 
  InfoKey                InfoData
  =====================  ===================
  LANGUAGE SUPPORT MODE  Standard
  RELEASE                16.10.00.03
  VERSION                16.10.00.03
  

  
  
  
• Find the Logged in Teradata server

  
  SELECT regexp_substr(regexp_substr(LogonSource,'[^ ]+',1,4),'[^;]+',1,1) Connected_Server
  FROM dbc.sessioninfo
  WHERE sessionno = (SELECT SESSION);
  

  
  
  
• List of all databases/users in Teradata

  
  select databasename from dbc.databases order by 1;
  

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

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

  
  
  
• Teradata Error Messages : To check error message for an error code.

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

  
  
  
• Identify All Roles assigned to Teradata Table

  
  SELECT databasename, tablename, rolename, AccessRight 
  FROM dbc.allrolerights
  WHERE  databasename = 'tutorial_db'
  AND    tablename= 'AccessRightsAbbreviation';
  

  
  
  
• Identify All rights for a Teradata user

  
  SELECT username, databasename, tablename, columnname, accessright, grantorname
  FROM dbc.allrights
  WHERE username='tutorial_user';
  

  
  
  
• Access Right Abbreviations: Below table list all the access right abbreviations in Teradata.

  Access Right Value	Description
  AE	ALTER EXTERNAL PROCEDURE
  AF	ALTER FUNCTION
  AN	ANY PRIVILEGE
  AP	ALTER PROCEDURE
  AS	ABORT SESSION
  CA	CREATE AUTHORIZATION
  CD	CREATE DATABASE
  CE	CREATE EXTERNAL PROCEDURE
  CF	CREATE FUNCTION
  CG	CREATE TRIGGER
  CM	CREATE MACRO
  CO	CREATE PROFILE
  CP	CHECKPOINT
  CR	CREATE ROLE
  CT	CREATE TABLE
  CU	CREATE USER
  CV	CREATE VIEW
  D	DELETE
  DA	DROP AUTHORIZATION
  DD	DROP DATABASE
  DF	DROP FUNCTION
  DG	DROP TRIGGER
  DM	DROP MACRO
  DO	DROP PROFILE
  DP	DUMP
  DR	DROP ROLE
  DT	DROP TABLE
  DU	DROP USER
  DV	DROP VIEW
  E	EXECUTE
  EF	EXECUTE FUNCTION
  GC	CREATE GLOP
  GD	DROP GLOP
  GM	GLOP MEMBER
  HR	HUT RELEASE LOCK
  I	INSERT
  IX	INDEX
  MR	MONITOR RESOURCE
  MS	MONITOR SESSION
  OP	CREATE OWNER PROCEDURE
  PC	CREATE PROCEDURE
  PD	DROP PROCEDURE
  PE	EXECUTE PROCEDURE
  RF	REFERENCES
  RS	RESTORE
  S	RETRIEVE/SELECT
  SR	SET RESOURCE RATE
  SS	SET SESSION RATE
  ST	STATISTICS
  U	UPDATE
  UM	UDT METHOD
  UT	UDT TYPE
  UU	UDT USAGE
  WL	WRITE LOCK