Useful DBC (Data Base Computer) System Views in Teradata

access_time 3 years ago visibility4730 comment 0

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

image

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

 image

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

image

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
info Last modified by Administrator at 2 months ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Kontext Column

Created for everyone to publish data, programming and cloud related articles.
Follow three steps to create your columns.


Learn more arrow_forward

More from Kontext

local_offer teradata local_offer SQL local_offer teradata-functions

visibility 2698
thumb_up 1
access_time 7 months ago

In ANSI SQL, you can use DATEADD function to easily add or subtract days/week/months/years from a date as the following code snippet shows: SELECT DATEADD ( month , 1 , '20060830' ); SELECT DATEADD ( day , - 1 , '20060831' ); However in Teradata this function is not implemented and you ...

local_offer teradata local_offer SQL local_offer teradata-sql-query

visibility 23
thumb_up 0
access_time 27 days ago

JSON data type is supported in Teradata from version 15.10. Together with native JSON type, a number of JSON functions are added to support extracting values from JSON, shredding JSON, etc.  info The following code snippets use string literal to demonstrate the usage of these functions; you ...

local_offer teradata local_offer SQL

visibility 550
thumb_up 0
access_time 11 months ago

JSON is commonly used in modern applications for data storage and transfers. Pretty much all programming languages provide APIs to parse JSON. 

About column

Tutorials and information about Teradata.

rss_feed Subscribe RSS