Useful DBC (Data Base Computer) System Views in Teradata

This page summarize some of the commonly used views in Teradata.

Conventions

In all the views in the following sections, X views are also available though they only return rows that contain information on objects that the requesting database user owns, created, granted privilige on, granted access through the current role, or granted access through nested roles.

For example, X view for dbc.ChildrenV is dbc.ChidrenVX.

Retrieve all the child databases recursively

dbc.ChildrenV lists all the descendent containers of a database.

For example, the following query retrieves all the child databases of database ParentDb:

SELECT  * 
FROM    DBC.ChildrenV
WHERE Parent='ParentDb';

For example, the following result set will be generated if the database hierarchy looks like the above screenshot:

Query database list

SELECT  * 
FROM    DBC.DatabasesV;

View DBC.DatabasesV returns information about all the databases. For example, database name, creator name, permanent space, spool space and temporary space.

Query tables, views, macros, procedures and user defined types lists

Retrieve these informtion by using the following query:

SELECT  * 
FROM    DBC.TablesV;

View DBC.TablesV includes information of creator, object name, database name, request text (DDL) and etc.

TableKind column indicates the object type. For example:

• T: Table
• V: View
• U: User-defined data type
• R: Table function
• M: Macro
• F: Standard function
• P: Stored procedure
• …

Query columns lists

SELECT  * 
FROM    DBC.ColumnsV
WHERE DatabaseName='DBC' AND TableName='Databases';

View DBC.ColumnsV can be used to retrieve column information.

Query functions list

View DBC.FunctionsV can be used to retireve contraints and indexes of a table:

SELECT  * 
FROM    DBC.FunctionsV;

Query hash and join indices lists

Use view DBC.IndicesV to query hash and join indices:

SELECT  * 
FROM    DBC.IndicesV
WHERE DatabaseName='DBC' AND TableName='RoleGrants';

Check table size

Use view DBC.TableSizeV to query size information for the tables:

SELECT  * 
FROM    DBC.TableSizeV 
WHERE DatabaseName='DBC' AND TableName='RoleGrants';

This view display permanent space by AMP (Vproc):

Check disk space size

View DBC.DiskSpaceV can be used to query disk space usage of databases by AMP(Vproc):

SELECT  * 
FROM    DBC.DiskSpaceV 
WHERE DatabaseName='DBC';

This view returns MAX, Current and Allocated space for the following types:

• Permanent
• Temporary
• Spool

Check user permissions/accesses

Use view DBC.AllRights to check the permission of a user:

SELECT  UserName,
    DatabaseName,
    TableName,
    ColumnName,
    AccessRight,
    GrantAuthority,
    GrantorName,
    AllnessFlag,
    CreatorName,
    CreateTimeStamp, 
        CASE 
            WHEN ACCESSRIGHT = 'D' THEN 'DELETE'
            WHEN ACCESSRIGHT = 'I' THEN 'INSERT'
            WHEN ACCESSRIGHT = 'R' THEN 'SELECT'
            WHEN ACCESSRIGHT = 'SH' THEN 'SHOW TABLE/VIEW'
            WHEN ACCESSRIGHT = 'U' THEN 'UPDATE' 
            ELSE 'OTHER - ' || ACCESSRIGHT
        END ACCESS_LEVEL
FROM    DBC.AllRights
WHERE   UserName='PUBLIC';

Other related views can be used:

• DBC.UserGrantedRightsV
• DBC.UserRightsV
• DBC.AllRoleRightsV
• DBC.UserRoleRightsV