How can I find all the underlying database tables from a report? 1

[abeena]

We are going through a database conversion and as part of the analysis process I need to find out all the underlying tables and columns used by each report.
The only way I know is to manually look at each data provider SQL for the table.column name. Is there a more efficient way to do this?

Does anybody know how to get this information by querying BO repository directly?

thank you!

 

[skrandel]

You can't get this by querying the repository. The code is not stored there. You certainly could do something at the report level using the DataProviderSQL function to see what comes after the FROM and before the WHERE.

But, in reality, shouldn't the universe tell you what tables you need?

Steve Krandel
Westbay Solutions

http://www.westbaysolutions.com

 

[blom0344]

Depending on your RDBMS you could attempt to 'capture' the SQL fired by the BO documents and store them for future research. If you monitor this over a period of time and set-up the capturing with the aid of a DBA you would end up with a list of active SQL's.

This is not very efficient if you have just about 10 SQL's , but with many hundreds it may pay off....

T. Blom
Information analyst
tbl@shimano-eu.com

 

[GeoffT]

Look at the Lyon Technologies web site (lyontechnologies.com). They have a free add-in that might help you find this information.