data dictionary

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
11.DBC.indexstats
12.DBC.Multicolumnstats :These are used to find stats info on given tables

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *