Wednesday, November 3, 2010

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 ;

10.  DBC.columnstats , DBC.indexstats  and DBC.Multicolumnstats
 These are used to find stats info on given tables

6 comments:

  1. How do I display list of Macros per database, using SQL and Data Dictionary?
    Thanks

    ReplyDelete
  2. select * from dbc.tables where tablekind ='M'
    and databasename =;

    ReplyDelete
    Replies
    1. What 'RequestText' column specifies in DBC.Tables Data Dictionary view

      Delete
    2. Abhishek,

      RequestText will keep the DDL structure of that particular object.

      Few things.
      1. If table is created for first time , then Table defination will be present in request text.
      2. If you altered the same table using "alter table add ..." then requesttext will be replaced with "alter table add..."

      Basically requesttext will contain details about latest modified object structure

      Delete

  3. I found your post very useful, thank you!
    Richard Brown electronic data room

    ReplyDelete