Normally I would use the DBCmonitor.Activity_Summary table to extract this information; however we disabled “turned off” access logging in June of 2008 to save space and CPU cycles.
I was told that I can extract this information using the QryLog tables. I tried writing a program and it’s not giving me what I need. This is what I have so far… Can someone provide me with a program that will count the number of times a View has been accesed (Select) in a specific database within the same year (2008).
sel l.UserName,ObjectDatabaseName,ObjectTableName from sys_mgmt.DBQLogtbl l, sys_mgmt.DBQLObjTbl o where o.ProcID = l.ProcID and o.QueryID = l.QueryID and (UPPER(QueryText) like 'SELECT%' and UPPER(QueryText) like '%BMMKTG%')
This is the SQL that works, like I mentioned above its only good until June 2008.
/* List all the tables that were accessed in any given time from a Database */ Select UserName, LogDate, LogTime, AccountName, DatabaseName, TableName, Sum(TypeCount) From dbcmonitor.VActivity_Summary Where logdate > '2008-01-01'and databasename IN ('BMMKTG') And accesstype = 'S' And TableName is Not Null Group By 1, 2, 3, 4, 5, 6 Order By 1, 2, 3, 4, 5, 6