I would like to analyze some of the SQL stored in the DBQLSqlTbl table. We have one or two tables we would like to do analysis on(Lets call it TABLEA)so we would need to check things like what other tables are joined to TableA what columns are TABLEA mostly joined on. What columns from TABLEA most appear in the where clause etc. I want to start writing something in C# but was wondering if prehaps someone has already gone through this effort before or has any better ideas then writing code to do this.
you would probably need to use wildcard searches as
querytext like '%tablename%columnname%'
I think, in DBQLSQLTBL, it would be querytext or sqltext column which contains query executed.
This won't be much useful if any aliases would be used for columnnames. Not completely reliable, will need manual efforts to check counts.
DBQLobjtbl_hst would be a good source to avoid the issue with aliases mentioned byu Dominiq in which you can use the objectdatabasename and objecttablename irrespective of the aliases. Take the logdate and queryid from this table and join with the DBQLObjtbl or DBQLSQLTBL for other details. The queries will be known from either of these tables but requires manual inspection to identify the joins and the tables associated with them.
We have thousands of queries we need to analyze to assess what tables are usually joined on. What columns are used in the where clause etc etc so doing this manually is not an option. The SQL most definetly uses aliases. I am sure there is some one out there who has done this before or somthing very similiar. Anybody ?
I have done it manually for huge number of tables. for the sake of addition of PPI.
If somebody can have a solution, it will surely help me adding next time... :-)
If anyone is intrested I wrote something in C# and it worked okay. not 100% accurate but close in terms of what tables join most often what is most often used in where clause etc etc. Inbox me if you intrested in the software.