By using this site, you acknowledge that you have read and understand our Cookie policy, Privacy policy and Terms .

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 Raymond at 2 years ago * This page is subject to Site terms.

More from Kontext

local_offer teradata local_offer SQL

visibility 6
thumb_up 0
access_time 3 days ago

OREPLACE functions in Teradata can be used to replace or remove characters from a string. OREPACE is Teradata's extension to ASNI SQL. The usual REPLACE function is not available. ANSI SQL REPLACE function REPLACE function is commonly implemented in many other SQL databases such as ...

open_in_new View open_in_new Code snippets

local_offer pyspark local_offer spark-2-x local_offer teradata local_offer SQL Server

visibility 59
thumb_up 0
access_time 18 days ago

In my previous article about  Connect to SQL Server in Spark (PySpark) , I mentioned the ways t...

open_in_new View open_in_new Spark + PySpark

local_offer teradata local_offer SQL

visibility 13
thumb_up 0
access_time 23 days ago

Extract sub string from a string is a common operation in data analytics. In Teradata, function SUBSTRING (SUBSTR) and REGEXP_SUBSTR are provided to achieve that. SUBSTR is used to extract string from a specified location while REGEXP_SUBSTR is used to extract string using regular expressions. ...

open_in_new View open_in_new Code snippets

local_offer teradata local_offer SQL

visibility 26
thumb_up 1
access_time 24 days ago

COALESCE function in Teradata returns NULL if all arguments evaluate to null; otherwise it returns the value of the first non-null argument. NULLIF is to used evaluate two expressions and returns NULL if the two arguments are equal otherwise if returns the first arguments. IS NULL i...

open_in_new View open_in_new Code snippets

info About author

Kontext dark theme mode

Dark theme mode

Dark theme mode is available on Kontext.

Learn more arrow_forward
Kontext Column

Kontext Column

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

Learn more arrow_forward
info Follow us on Twitter to get the latest article updates. Follow us