Is there an easy way to find a particular table is used in a...

[Praveen Lazer]

Is there an easy way to find a particular table is used in any of the reports already out there How to do that search rather than manually going and look for that in every report

Version: 8.2.04

[NYCBabak .]

Using App Studio, take a look at Impact Analysis. Itll let you search both the Repository and the Server content to find where a master file is used.

[Toby Mills]

Hi Praveen - welcome to myibi.

Impact Analysis that @nycbabak suggested will work for you if your reports are written making use of Master files. If your report writers use SQL inside their reports, youll want to come up with another way of searching.

If they use SQL passthru, you might use your favorite string searching tool to search the reporting servers approot for any mention of the table (typically this is /ibi/apps/).

Searching the repository could be done directly by directly reading the repository tables, or simply by making a Change Management package and exporting it. Search the package for the table name and youll be able to make out what the pathname is of the file where the string was found.

Let us know if this helps with your question.

Toby

[Debra Waybright]

Here is some SQL I use to search the Repository for table or field names:

SELECT

T2.PRT_PATH

, T2.OBJNAME

, T2.LASTACCESSON

, T2.LASTACCESSBY

, T2.LASTMODON

FROM WF_CONTENT_REVS T1

INNER JOIN WF_REPOSOBJ T2 ON T1.OBJ_HANDLE=T2.HANDLE

-*type the search string in upper case between the %

WHERE UPPER(CAST(CAST(T1.BCONTENT AS VARBINARY(MAX)) AS VARCHAR(MAX))) LIKE %STG_CLAIM_COUNT_DETAIL%

Hope this helps.

-Deb

[Praveen Lazer]

debra.waybright:

 

SELECT

T2.PRT_PATH

, T2.OBJNAME

, T2.LASTACCESSON

, T2.LASTACCESSBY

, T2.LASTMODON

FROM WF_CONTENT_REVS T1

INNER JOIN WF_REPOSOBJ T2 ON T1.OBJ_HANDLE=T2.HANDLE

-*type the search string in upper case between the %

WHERE UPPER(CAST(CAST(T1.BCONTENT AS VARBINARY(MAX)) AS VARCHAR(MAX))) LIKE %STG_CLAIM_COUNT_DETAIL%

 

 

Thanks for the response