I am using DBQLOGTBL and DBQLSQLTBL to check sql query performance.
However in many queries the databasename is not mentioned. May be because default database is set in previous query so subsequent queries have only table names and no databasename mentioned in it.
Is there a way to find out which all databases were used for any query present in SQLTEXTINFO columns ? I have checked defaultdatabase column too however that is not giving the desired result.
Solved! Go to Solution.
Joining to dbqlobjtbl would be ideal since the database will be logged even if it is a default database for that query.
Of course object logging would need to be enabled.
Hi, If the DBQLObjTbl logging is not activated then is there a work around to know the list of objects (tables, columns) accessed by a user?
Getting the default database name is easy because it it stored in the dbqlogtbl: DefaultDatabase
Finding all the tables and coluns referenced would require a resoruce intensive matching of sql to column and view data like we tried to do in the old days.
It was very hit or miss and the reason for the existence of the dbqlobjtbl :)
Just get that activated. There is no reason performance wise not to and tons of reasons to have it.
Thanks, I was using a R library to parse the SQLTEXT and it is time consuming and lots of special characters are there to handle it. Other way the output produces next level of insights.
To keep it simple, I have requested our DBA to enable logging for DBQLOBJTBL.
I am not sure whether someone has done analysis on SQLTEXT or QUERYTEXT parsing to get patterns of SQLs. It will be really an interesting topic.
If the object table was not enabled because it contains too much details & tends to grow very fast, there's a new option in TD15.10, NO COLUMN OBJECTS, not logging column level information.
Only challenge I am facing now is the information does not differentiate between Source and Target.
I mean if I have to identify the Target Database/Table and Source Database/Table then I cannot find any flag to identify it.
In DBQLOBJTBL it is saving all the details in similar manner.
It would be great to identify Target DBNAME and Source DBNAME.