Re: find columns used in procedures

On 16 Dec 2004 01:55:47 -0800, dmadeka_at_gmail.com wrote:

>Hi,
>I have a set of PL/SQL packages in an application that I inherited (
>about 8 KLOC's). This code refers to a set of really fat ( large no of
>columns ) datamart tables. For performance and maintainability was
>wanted to just have the columns in the mart which are used by the
>PL/SQL packages, dropping the unused ones. Is there any way I can find
>out which columns of which tables are used in the PL/SQL code.
>An pointers would be appreciated.
>
>Regds
>Dev
>

Really a challenge.

If we assume it's a read-only thing appl., containing only SELECT's, you may parse the code for the SELECT keyword and register the column names and the belonging tables afterwards. BUT :

What if 'SELECT *' appears ?
What if the code contains dynamic SQL ?
What if there exist check constraints referring to columns not being referred in the code ?
What if triggers or views refer columns not referred to in the code...?
What about the system that feeds you Mart, will it "miss" those columns you drop ?
etc, etc...

I guess you will run out of luck on this, if not sooner, then later...

• Kenneth Koenraadt