Re: find columns used in procedures

From: Denis Do <nospam.denisdo_at_yahoo.com>
Date: Fri, 17 Dec 2004 06:14:39 GMT
Message-ID: <slrncs4uce.20k.nospam.denisdo@denisdo.news.google.com>

On 2004-12-16, Kenneth Koenraadt <Kenneth> wrote:
> On 16 Dec 2004 01:55:47 -0800, dmadeka_at_gmail.com wrote:
>
>>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.

Okay, here is a *really* stupid way :-), but it will save you alot of time.

Make an export of your database with rows=no and import "empty placeholders" into fresh DB.(or use any full-size test system - whatever you like)

Recompile all obejcts and check that all of them are valid. Start dropping table one after another and note which pl/sql gets invalidated - those tables are referenced by these programs.

Since you limit your search area to set of tables, you can go through code to check columns.

Sounds crazy enough? :0-) Received on Fri Dec 17 2004 - 00:14:39 CST