Wednesday, May 7, 2014

Getting a List of Tables accessed by a User in Teradata

Some times for reporting and management purposes one may need to see a list of tables and views (Database Objects) viewed by a particular user. This can be achieved by making an inner join between dbc.dbqlObjTbl and dbc.qryLog table.
The following query gets a list of Views in database='test' accessed by a userName='shahf'.

SELECT  DISTINCT a.objectDatabaseName,a.objectTableName , b.userName ,  b.queryText, b.logDate ,  b.startTime  
FROM   dbc.dbqlobjtbl  a 
INNER JOIN dbc.qrylog  b 
ON        a.queryid = b.queryid 
AND       a.procid = b.procid 
AND       a.objectDatabaseName = 'test' 
AND       a.objectType = 'Viw'
AND  b.userName ='shahf'

Modifying the objectType appropriately in the above query can bring a list of Objects like database, data table, column, secondary index, join index, or journal table a user has accessed. If the object is a secondary index, its number is returned rather than a name.

Note: Any DBC database tables and columns used by the system while processing a query are not reflected in the DBQL object rows for that query. This means, for example, that statements like CREATE TABLE or SELECT FROM will not have objects logged through DBQL because they deal with DBC tables and columns. So DBC tables accessed by a user cannot be found with the above query.

For further information on the dbc.dbqlobjtbl works refer to the link given below.