PROVE IT !!If you know it then PROVE IT !! Skill Proficiency Test

Important Data Dictionary View's in Teradata

ACCESSLOG                    
The DBC.AccessLog view supplies information from the access log. Entries are made in the access log as a function of the rules entered by BEGIN/END LOGGING statements.
ACCLOGRULES                  
The DBC.AccLogRules view provides information on the access logging rules entered as a result of BEGIN/END LOGGING statements.  Columns in the view are: UserName, DatabaseName, TVMName and AcrCheckpoint…AcrRepControl.

Complete list of data dictionary views are available below…

ACCESSLOG                    
The DBC.AccessLog view supplies information from the access log. Entries are made in the access log as a function of the rules entered by BEGIN/END LOGGING statements.
ACCLOGRULES                  
The DBC.AccLogRules view provides information on the access logging rules entered as a result of BEGIN/END LOGGING statements.  Columns in the view are: UserName, DatabaseName, TVMName and AcrCheckpoint…AcrRepControl.
ACCOUNTINFO                  
Each row of the DBC.AccountInfo view provides a valid account name for a user. The column names are: UserName, AccountName and UserOrProfile.
ALLRIGHTS                     
The DBC.AllRights view gives the access rights for all user on the system. The column names are: UserName, DatabaseName, TableName, FieldName, AccessRight, WithGrantOption, GrantorName and AllnessFlag.
ALLROLERIGHTS                
The AllRoleRights view provides information about the rights granted to each role. The column names are: UserName, DatabaseName, TableName, ColumnName, AccessRight, GrantAuthority, GrantorName and CreateTimeStamp.
ALLSPACE                     
The DBC.AllSpace view provides information about disk space allocation and usage.  The columns are: Vproc, DatabaseName, AccountName, TableName, MaxPerm, MaxSpool MaxTemp, CurrentPerm, CurrentSpool, CurrentTemp, PeakPerm, PeakSpool and PeakTemp.
ALLTEMPTABLES                
The DBC.AllTempTables view provides information about all temporary tables materialized in the system. The column names are: HostNo, SessionNo, UserName, B_DatabaseName, B_TableName and E_TableId.
ALL_RI_CHILDREN              
The All_RI_Children view provides information about tables in child-parent order. The columns names are: IndexID, IndexName, ChildDB, ChildTable, ChildKeyColumn, ParentDB, ParentTable, ParentKeyColumn, InconsistencyFlag, CreatorName and CreatTimeStamp.
ALL_RI_PARENTS               
The All_RI_Parents view provides information about tables in parent-child order. The columns names are: IndexID, IndexName, ParentDB, ParentTable, ParentKeyColumn, ChildDB, ChildTable, ChildKeyColumn, InconsistencyFlag, CreatorName and CreatTimeStamp.
AMPUSAGE                     
Each row of the DBC.AMPUsage view provides information about the resources used in an AMP Vproc by user and account.  The column names are: Username, AccountName, CPUTime, DiskIO, Vproc, VprocType, Model.
ARCHIVELOGGINGOBJSV          
Each row of DBC.ArchiveLoggingObjsV view provides information about a table that has an online archive active log.
ASSOCIATION                  
The DBC.Association view allows the user to retrieve information about a table which has been imported from another DBC.
AUTHORIZATIONS               
Each row of the DBC.Authorizations view provides information about an authorization. The column names are: DatabaseName, AuthorizationName, AuthorizationId, TableKind, Version, AuthorizationType, AuthorizationSubType, OSDomainName and UserName
CHARSETS                     
The DBC.CharSets view provides information about character sets that exist on the DBC.  There is only one column: CharSetName.
CHARTRANSLATIONS             
The DBC.CharTranslations view provides information about character sets supported by the DBC.  The columns are CharSetName, CharSetId, InstallFlag, E2I, E2IUp, I2E, I2EUp.
CHILDREN                     
Lists the names of Databases and users and their Parents in the hierarchy.
COLLATIONS                   
DBC.Collations view provides a view on all columns of the DBC.CollationTbl.
COLUMNS                      
Each row of the DBC.Columns view provides information about a table or view column, or a stored procedure or a macro parameter.  Some of the column names are: DatabaseName, TableName, ColumnName, ColumnFormat, ColumnTitle, ColumnType, DefaultValue.
COLUMNSTATS                  
The DBC.ColumnStats view provides statistics information on each table. Some of the columns are:  FieldStatistics, DatabaseName, TableName , SeqNumber, ColumnName, ColumnType, ColumnLength, ColumnFormat, IndexStatistics
COSTPROFILES_V               
Defined Cost Profile Instances. Provides the cost profile instances available for use by the optimizer. Cost profile instances contain the actual values to be used in the cost profile.
COSTPROFILETYPES_V           
Defined Cost Profile Types. Provides the profile type name and description for each cost profile type.
COSTPROFILEVALUES_V          
Defined values for constants in a cost profile instance. Provides the profile name, ID and the constant name, ID, category, value and description.
CSPSESSIONINFO               
The CSPSessionInfo view provides information from DBC.SessionTbl for the CRASHDUMPS user when using the dump COPY SAVE PROGRAM (CSP). This view is usually not referenced directly by users.
DATABASES                    
Each row of the DBC.Databases view provides information about a data base. The column names are: DatabaseName, CreatorName,        OwnerName, ProtectionType, PermSpace, SpoolSpace, TempSpace and CommentString.
DATABASE_DEFAULT_JOURNALS    
Each row of the view defines a database which has a default journal specification.  The columns are: DatabaseName, Journal_DB and JournalName.
DBCINFO                      
The DBC.DBCInfo table contains information about the DBC system.
DBQLRULES                    
Each row is one DBQL rule for one User / AccountString combination.
DELETEACCESSLOG              
Purges entries from the access log that are at least 30 days old.
DELETEOLDINDOUBT             
The DELETEOLDINDOUBT view allows a user to delete all rows older than 30 days from the InDoubtResLog table.
DISKSPACE                    
The DBC.DiskSpace view provides information about disk space allocation and usage by AMP, database (user) and account.  The column names are: Vproc, DatabaseName, AccountName, MaxPerm, MaxSpool, CurrentPerm, CurrentSpool, PeakPerm and PeakSpool.
ERRORTBLSV                   
The DBC.ErrorTblsV view provides information about relationships between data and error tables: ErrTblDbName, ErrTblName, BaseTblName, CreatorName, CreateTimeStamp.
EVENTS                       
The DBC.Events view provides information about Host Utility activites.
EVENTS_CONFIGURATION         
The DBC.Events_Configuration view provides information about Utility events which did not affect all AMPs.  It is a join of the RCEvent and RCConfiguration tables.  The first five columns of the view are the same as for the Events view.
EVENTS_MEDIA                 
The DBC.Events_Media view provides information about Utility events which involved removable media. This view joins the RCEvent and RCMedia tables.  The first six columns of the view are the same as for the Events view.
EXTERNALSPS                  
Each row of the DBC.ExternalSPs view provides information about an External Stored Procedure. The column names are: DatabaseName, ExternalProcedureName, NumParameters, ExternalProcedureId, ExternalName, SrcFileLanguage, NoSQLDataAccess, etc.
FUNCTIONS                     
Each row of the DBC.Functions view provides information about a function. The column names are: DatabaseName, FunctionName, SpecificName, NumParameters, FunctionId, ParameterDataTypes, FunctionType, ExternalName, SrcFileLanguage, NoSQLDataAccess, etc.
HARDWARE_EVENT_LOG           
The DBC.Hardware_Event_Log view provides information about system failures,  error conditions, and other events.
HOSTSINFO                    
The DBC.HostsInfo view provides information about DBC hosts.  The columns are HostId, HostName, and DefaultCharSet.
INDEXCONSTRAINTS             
The IndexConstraints view provides information about index constraints (implied by the partitioning expressions for an index): Database/Table/IndexName, IndexNumber, ConstraintType/Text/Collation, CollationName, CreatorName, and CreateTimeStamp.
INDEXSTATS                   
The DBC.IndexStats view provides information about statistics on the indexes defined for a table.Some of the column names are: DatabaseName, TableName, IndexNumber, IndexName, ColumnPosition, ColumnName, ColumnType, IndexStatistics
INDICES                      
The DBC.Indices view provides information about indexes on tables. The column names are: DatabaseName, TableName, IndexNumber, IndexType, UniqueType, IndexName, ColumnName, and ColumnPosition.
INDOUBTLOG                   
The DBC.InDoubtLog view provides information about the sessions commited or rollbacked through the Resolver Base Module.
JOININDICESV                 
Each row of Dbc.JoinIndicesV view provides information about a join index defined on a table.
JOININDICESV                 
Each row of Dbc.JoinIndicesV view provides information about a join index defined on a table.
JOURNALS                     
Each row of the DBC.Journals view provides information about a data table for which journal images are to be created.  Column names are: Tables_db, TableName, Journals_DB and JournalName.
LOGONOFF                     
The DBC.LogOnOff view provides information about Logon and Logoff events. The column names are: LogDate, LogTime, Username, AccountName, Event, LogicalHostId, IFPNo, SessionNo, LogonDate, LogonTime and LogonSource.
LOGONRULES                   
The DBC.LogonRules view provides information on logon control rules entered via GRANT/REVOKE LOGON statements.  Columns are: UserName, LogicalHostId, LogonStatus and NullPassword.
MULTICOLUMNSTATS             
The DBC.MultiColumnStats view provides information about statistics collected on a set of non-indexed columns.Some of the column names are: DatabaseName, TableName, StatisticsId, ColumnPosition, ColumnName, ColumnType, ColumnsStatistics
OWNERDB                      
The OwnerDB view is used by other views and is usually not referenced directly by users.
PROFILEINFO                  
The DBC.ProfileInfo view provides information about all profiles created in the system.
QRYLOG                       
Each row in the QryLog contains DBQL default information for a query.
QRYLOGEVENTHIS               
Each row in the QryLogEventHis view contains a row describing a attribute of an event
QRYLOGEVENTS                 
Each row in the QryLogEvents view contains a TDWM event that could affect system performance.
QRYLOGEVENTSV                
Each row in the QryLogEventsV view contains a TDWM event that could affect system performance.
QRYLOGEXCEPTIONS             
Each row in the QryLogExceptions view contains a query exception as defined through Workload Definitions .
QRYLOGEXPLAIN                
The QryLogExplain view contains explain text for the query.
QRYLOGOBJECTS                
Each row in the QryLogObjects view contains an object referenced by a query.
QRYLOGSQL                    
The QryLogSQL view contains the SQL of a query.
QRYLOGSTEPS                  
The QryLogSteps view contains DBQL information on a step processed by a query.
QRYLOGSUMMARY                
Each row in the QryLogSummary view contains summarized DBQL query information.
QRYLOGTDWM                   
Each row in the QryLogTDWM contains TDWM information for a query.
QRYLOGTDWMSUM                
Each row in the QryLogTdwmSum view contains a summary of WD  activity for a predetemined period.
QRYLOGXMLV                   
Contains query plans in XML format.
REPCAPTURERULESV             
Each row of DBC.RepCaptureRulesV view provides information about a rule on an object in a ruleset for a replication group.
REPTABLES                    
The DBC.RepTables view provides a list of all replicated tables in the system.
RESCPUUSAGEBYAMPVIEW         
View of CPU usage by AMP
RESCPUUSAGEBYPEVIEW          
View of CPU usage by PE
RESGENERALINFOVIEW           
View of general system information
RESOLVEDDTS                  
The DBC.ResolvedDTS view provides information about last resolved  CURRENT_DATE and CURRENT_TIMESTAMP of the table. The columns are  ResolvedCurrent_Date, ResolvedCurrent_TimeStamp, TableName, and  DatabaseName.
RESSAWTVIEW                  
View of Sawt table data
RESSHSTGROUPVIEW             
View of Host Channel and Lan Activity with Node Groups
RESSPSVIEW                   
View of Sps table data
RESSVPRVIEW                  
View of ResUsageSvpr (per Vproc) table data
RI_CHILD_TABLES              
The RI_Child_Tables view provides information about tables in child-parent order (IDs are returned instead of names). The column names are: IndexID, IndexName, ChildDbID, ChildTID, ChildKeyFID, ParentDbID, ParentTID, etc.
RI_DISTINCT_CHILDREN         
The RI_Distinct_Children view provides information about tables in child-parent order without duplicates resulting from multi-column foreign keys. The column names are: IndexID, IndexName, ChildDB, ChildTable, ParentDB, ParentTable, etc.
RI_DISTINCT_PARENTS          
The RI_Distinct_Parents view provides information about tables in parent-child order without duplicates resulting from multi-column foreign keys. The column names are: IndexID, IndexName, ParentDB, ParentTable, ChildDB, etc.
RI_PARENT_TABLES             
The RI_Parent_Tables view provides information about tables in parent-child order (IDs are returned instead of names). The column names are: IndexID, IndexName, ParentDbID, ParentTID, ParentKeyFID, ChildDbID, ChildTID, etc.
ROLEINFO                     
The RoleInfo view provides information about roles. The column names are: RoleName, CreatorName, CommentString and CreateTimeStamp.
ROLEMEMBERS                  
The RoleMembers view provides information about members of roles. The column names are: RoleName, Grantee, GranteeKind, Grantor, WhenGranted, DefaultRole and WithAdmin.
SECURITYDEFAULTS             
The DBC.SysSecSefaults table contains site options controlling use of DBC passwords.
SECURITYLOG                  
The SecurityLog view is obsolete, use AccessLog.  This view is currently provided to maintain compatability with previous releases. The column names are LogDate, LogTime, LogType, UserName, AccountName, DatabaseName, TableName and Text.
SESSIONINFO                  
The DBC.SessionInfo view provides information about current sessions.
SHOWCOLCHECKS                
The ShowColChecks view provides information about column constraints. The columns names are: DatabaseName, TableName, ColumnName, ColCheck, CreatorName and CreateTimeStamp.
SOFTWARE_EVENT_LOG           
The DBC.Software_Event_Log view provides information about system failures,  error conditions, and other events.
TABLES                       
Each row of the DBC.Tables view provides information about a table, view, stored procedure or macro.  The column names are: DatabaseName, TableName, TableKind, CreatorName, ProtectionType, RequestText, CommentString, QueueFlag, CommitOpt and TransLog.
TABLESIZE                    
Each row of the DBC.TableSize view provides information about table space usage in an AMP by database and table.  The column names are: Vproc, DatabaseName, TableName, CurrentPerm and PeakPerm.
TABLETEXT                    
The TableText view provides compete RequestText for tables, views and macros. The column names are: DatabaseName, TableName, TableKind, RequestText and LineNo.
TABLE_LEVELCONSTRAINTS       
The Table_LevelConstraints view provides information about table constraints. The columns names are: DatabaseName, TableName, ConstraintName, ConstraintText, CreatorName and CreateTimeStamp.
TRIGGERS                     
The Triggers view provides information about triggers. The column names are: DatabaseName, TableName, TriggerName, EnabledFlag, ActionTime, Event, Kind, OrderNumber, TriggerComment, RequestText, CreatorName, CreateTimeStamp, etc.
USERDB                       
The UserDB view is used by other views and is usually not referenced directly by users.
USERGRANTEDRIGHTS            
The DBC.UserGrantedRights view provides information on access rights that the current user has granted to other users.  The column names are: DatabaseName, TableName, FieldName, Grantee, AccessRight, WithGrant and AllnessFlag.
USERRIGHTS                   
The DBC.UserRights view gives the access rights that have been granted to the current user.  The column names are: DatabaseName, TableName, FieldName, AccessRight, WithGrant and GrantorName.
USERS                        
Each row of the DBC.Users view provides information about a user. The column names can be obtained by using the HELP VIEW DBC.Users.
USER_DEFAULT_JOURNALS        
Each row of the view defines a user which has a default journal specification.  The columns are: UserName, Journal_DB and JournalName.