PROVE IT !!If you know it then PROVE IT !! Skill Proficiency Test

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

Add a Comment

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