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
Very Helpful, Thanks!!!
ReplyDeleteHow do I display list of Macros per database, using SQL and Data Dictionary?
ReplyDeleteThanks
select * from dbc.tables where tablekind ='M'
ReplyDeleteand databasename =;
What 'RequestText' column specifies in DBC.Tables Data Dictionary view
DeleteAbhishek,
DeleteRequestText 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
ReplyDeleteI found your post very useful, thank you!
Richard Brown electronic data room