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

More from Kontext

local_offer teradata local_offer python

visibility 623
thumb_up 1
access_time 3 months ago

Pandas is commonly used by Python users to perform data operations. In many scenarios, the results need to be saved to a storage like Teradata. This article shows you how to do that easily using JayDeBeApi or  ...

open_in_new Spark + PySpark

local_offer teradata local_offer SQL

visibility 59
thumb_up 0
access_time 3 months ago

In SQL Server, we can use TRUNCATE statement to clear all the records in a table and it usually performs better compared with DELETE statements as no transaction log for each individual row deletion. The syntax looks like the following: TRUNCATE TABLE { database_name.schema_name.tab...

open_in_new Code snippets

local_offer teradata local_offer python local_offer Java

visibility 348
thumb_up 0
access_time 3 months ago

Python JayDeBeApi module allows you to connect from Python to Teradata databases using Java JDBC drivers. In article Connect to Teradata database through Python , I showed ho...

open_in_new Python Programming

local_offer teradata local_offer SQL

visibility 240
thumb_up 0
access_time 4 months 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 Code snippets

info About author

comment Comments (0)

comment Add comment

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

No comments yet.

Dark theme mode

Dark theme mode is available on Kontext.

Learn more arrow_forward

Kontext Column

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


Learn more arrow_forward