List of useful Data dictionary views

List of useful Data dictionary views

List of useful Data dictionary views which might come in handy in situations!

1. DBC.users 

This view gives current user information

2. dbc.sessioninfo

This view gives information about

- details of  users currently logged in

3.DBC.Databases

This view list all the databases present in the given teradata database system. ALso contains useful information like

-Creatorname

-OWnername

-PERMspace

-SPOOLspace

-TEMPspace

4.DBC.Indices

It gives information on the index created for given table

5.DBC.Tables

It gives information about all the Tables(T), views(V), macros(M), triggers(G), and stored procedures .

6.DBC.IndexConstraints

It Provides information about partitioned primary index constraints.

'Q' indicates a table with a PPI

7. DBC.DiskSpace

It provides information about disk space usage (including spool) for any database or account.

SELECT      DatabaseName

,CAST (SUM (MaxPerm) AS FORMAT 'zzz,zzz,zz9')

        ,CAST (SUM (CurrentPerm) AS FORMAT 'zzz,zzz,zz9')

        ,CAST (((SUM (CurrentPerm))/

                NULLIFZERO (SUM(MaxPerm)) * 100)

                AS FORMAT 'zz9.99%') AS "% Used"

FROM   DBC.DiskSpace

GROUP BY   1

ORDER BY   4 DESC ;

8. DBC.TableSize

It provides information about disk space usage (excluding spool) for any database, table or account

SELECT      Vproc

        ,CAST (TableName

                 AS FORMAT 'X(20)')

        ,CurrentPerm

        ,PeakPerm

FROM   DBC.TableSize

WHERE DatabaseName = USER

ORDER BY           TableName, Vproc ;

9. DBC.AllSpace

It provides information about disk space usage (including spool) for any database, table, or account.

SELECT      Vproc

        ,CAST (TableName AS

                FORMAT 'X(20)')

        ,MaxPerm

        ,CurrentPerm

FROM   DBC.AllSpace

WHERE DatabaseName = USER

ORDER BY   TableName, Vproc ;