The DBC.DBCInfoV ViewUse this view to find out what version of Teradata you are on. This is a V13 system.

Restricted Views have an X at the End of their Name

With V12, they introduced a V at the end of some Data Dictionary Views. These are the newer views. The only difference is that the previous views always displayed in Char(30). The newer views (with a V) have a data type of VARCHAR(128), which can display information associated with UNICODE.

Newer V Views introduced in V12 and can display information using UNICODE.

The DBC.DatabasesV View

SELECT DatabaseName(CHAR(10)) AS “Name”              ,CreatorName(CHAR(10)) AS “Creator”              ,CreateTimeStamp               ,PermSpace(FORMAT ‘zzz,zz9,999’)              ,SpoolSpace(FORMAT ‘zzz,zz9,999’)              ,TempSpace(FORMAT ‘zzz,zz9,999’)              ,DBKind FROM DBC.DatabasesVWHERE DatabaseName like ‘SQL%’ORDER BY 1;

This View shows Database, user, and immediate parent information plus some great information about Space among other things.

The DBC.Users View

SELECT UserName              ,CreatorName              ,PermSpace              ,SpoolSpace              ,TempSpace              ,LastAlterTimeStampFROM DBC.UsersWHERE UserName = USER ;

The DBC.Tables View

This view is quite similar to the Databases view but includes columns specific to users.

The DBC.Tables ViewSELECT TRIM(DatabaseName) || ‘.’ || TableName AS “Fully Qualified Name” ,TableKindFROM    DBC.TablesVWHERE Databasename IN ( ‘SQL_Class’, ‘SQL_Views’)AND      TableName like ‘Employe%’;

This view shows tables, views, macros, triggers, and stored procedures information.

Show the tables in the SQL_Class Database that are Fallback Protected

SELECT Count(*)FROM   DBC.TablesVWHERE DatabaseName = ‘SQL_Class’AND     TableKind IN(‘T’, ‘O’)AND      ProtectionType = ‘F’;

Show the tables in the SQL_Class Database that are NOT Fallback Protected

SELECT Count(*)FROM DBC.TablesVWHERE DatabaseName = ‘SQL_Class’AND TableKind IN(‘T’, ‘O’)AND ProtectionType = ‘N’;

The DBC.Indices View

The DBC.Columns View

New V14 – The DBC.PartitioningConstraintsV View

This view shows columns and their parameters in tables, views, and macros.

SELECT TableName              ,ColumnPartitioningLevel AS “Level”              ,PartitioningLevels AS “#Levels”              ,ConstraintText AS “ConText”FROM   DBC.PartitioningConstraintsVWHERE DatabaseName = ‘SQL_Class’;

Show the number of tables in the SQL_Class database that are partitioned

SELECT COUNT(*)FROM DBC.PartitioningConstraintsVWHERE ColumnPartitioningLevel = 0;

Show the number of tables in the entire system that are partitioned

SELECT COUNT(*)FROM DBC.PartitioningConstraintsVWHERE ColumnPartitioningLevel <> 0;

Show the number of tables in the entire system that have column partitioning

The DBC.AccountInfo View

UserName , AccountName , UserOrProfile

The DBC.AMPUsage View

SELECT COUNT(DISTINCT (Vproc)) AS “# of AMPs// in our System”FROM DBC.AMPUsageV WHERE UserName = USER;

The DBC.AllTempTables

This table is only for Global Temporary Tables

The DBC.Triggers

The DBC.All_RI_ChildrenV

DBC.SessionInfoV Information

SELECT COUNT(*)FROMDBC.SessionInfoV;

Count the number of sessions currently running on the system

SELECT * FROM DBC.SessionInfoV

WHERE UserName = USER;

Show me everything about my sessions

SELECT UserName              ,COUNT(*)FROM DBC.SessionInfoV

GROUP BY 1ORDER BY 2 ;

Show each User and the number of sessions that they have running that are currently on the system.

DBC.LogonOffV

 

AllRightsV,AllRoleRights,UserRightsV,UserGrantedRightsV,

DBC.Profiles View,RoleMembers, RoleInfo, UserRoleRights and ProfileInfoVX,

AllRightsV, 

SELECT COUNT(*)FROM DBC.AllRightsV;

lists all rights granted to users in the system.

AllRoleRights,

SELECT * FROMDBC.AllRoleRightsV;

lists all rights granted to roles in the system.

UserRightsV

SELECT COUNT(*)FROM DBC.UserRightsV;

lists all rights granted to the current user.

SELECT * FROM DBC.UserRightsV

WHERE Databasename = ‘sql00’;

Shows all the Access Rights for user SQL00

UserGrantedRightsV

SELECT Count(*)FROM

DBC.UserGrantedRightsV;

Show the number of Access Rights that you explicitly granted

SELECT * FROMDBC.UserGrantedRightsV;

Shows all the Access Rights that you have explicitly granted

DBC.Profiles View,RoleMembers,UserRoleRights,RoleInfo,  and ProfileInfoVX,

The DBC.Profiles View

This view shows information about Profiles.

RoleMembers

SELECT * FROMDBC.RoleMembersVX;

Check to see your role names, the grantor and the date it was granted

UserRoleRights

SELECT RoleName, DatabaseName,TableName, ColumnName, AccessRightFROM DBC.UserRoleRightsVORDER BY 1;

lists all rights granted to the enabled roles of the user.

RoleInfo

SELECT COUNT(*)FROM DBC.RoleInfoVX;

Check to see all the roles that you personally have created

ProfileInfoVX

SELECT * FROMDBC.ProfileInfoVX;

Show all information about your profiles

SELECT Role;

Check to see if you have a Role

SELECT Profile;

Check to see if you have a profile

The RoleMembers, UserRoleRights, RoleInfoVX and ProfileInfoVX views show you what you need to know about Roles and Profiles. Remember that Roles are for “Rights” and Profiles are for “People”.

Understanding that Space is based on a Per-AMP Basis

The UpdateSpace utility is a utility designed to fix inconsistencies in the DBC.DatabaseSpace table, which might occur occasionally. This is run from the DB WindowFrom supervisor window: START UPDATESPACE

DBC.Tablesize

When statistics are collected on a system, those statistics are held in the User DBC. Statistics

DBC Tables used in the Collect Statistics Process

DBC.TVFields–holds statistics collected for single column or single column indexes.DBC.Indexes–holds statistics collected for multi-column or multi-column indexes.DBC.StatsTbl(NEW 14.0)–repository for the statistics management data in V14.

EXEC DBC.ClearPeakDisk;

The DBA will run the above Macro once a month to reset the Peak values for Perm, Spool and Temp for everyone

The DBC.AssociationV View

When the COPY command is used to copy an object from one Teradata system to another, it is recorded in the DBC.AssociationV View.

The DBC.JournalsV View

DBC.Databases2V is for Unresolved Reference Constraints

The DBC.Databases2[V][X] views show all unresolved reference constraints, which are caused by creating a table with a Foreign Key before creating the table with the Primary Key. Or, it can happen on a restore if the Foreign Key is restored and the Primary Key table is not.

This View shows all inconsistencies with Referential Integrity. You need to restore the Parent Table (where the Primary Key resides) to “resolve” the references constraint.

The DBC.All_RI_ChildrenV for Inconsistent RI

This view is used to identify tables with Referential Integrity from the child to the parent and can also be used to show if the RI constraint is consistent (good) or inconsistent (bad).

There are two options to fix Referential Integrity problems:

1) ALTER TABLE Tablename DROP INCONSISTENT REFERENCES;Foreign Key references are dropped. Use another ALTER statement to reproduce them

.2) Use the REVALIDATE REFERENCES command in the Archive Facility (ARC).

The DBC.ShowColChecksV View

This view shows columns that have checks inside their DDL.

The DBC.ShowTblChecksV View

This view shows table level check constraints that are “named” column constraints.

This view shows table level check constraints that are “named” column constraints.

The DBC.PartitioningConstraintsV View

Show Partitioning Constraints for a certain database

The DBC.AccessLogV View

This view shows information about the AccessLog when Access Logging is turned on.

DBC.DBQLRulesV

BEGIN QUERY LOGGING WITH SQL, EXPLAIN ON ALL;

Log default rows, SQL and Explain for ALL Queries.

DBC.QryLogV

DBC.QryLogSummaryV

he DBC.IdCol Table

When an Identify column is created, a row is placed into the table DBC.IdCol.