Re: find columns used in procedures

Looks good. Will try it out and let you know the results. One step that I would add is finding and removing all the "Select *" code with explicit column names.

Will keep you posted.

Regards
Dev

Denis Do wrote:
> On 2004-12-18, nullpointer <null_pointer_at_rediffmail.com> wrote:
> > Also dropping columns might not work because of the "SELECT * FROM
> > table". It would still compile the PL/SQL but would break my app.
>
> Just tested it, will work for dropping columns - please have a look
>
> SQL> desc t2
> Name Null? Type
> ---------------------- -------- ---------------
> ID NOT NULL NUMBER(5)
> ADDRESS NOT NULL VARCHAR2(2000)
> DESCRIPTION NOT NULL VARCHAR2(2000)
>
> SQL>
> create or replace procedure showall as
> begin
> dbms_output.put_line(' ... showing user data ...');
> for item in (
> select *
> from t2)
> loop
> dbms_output.put_line(' .... ');
> end loop;
> end;
> /
>
> SQL> @/tmp/3
>
> OBJECT_TYPE OBJECT_NAME STATUS
> ------------------- ---------------------------------------- -------
> TABLE T1 VALID
> INDEX SYS_C005285 VALID
> INDEX SYS_C005282 VALID
> TABLE T2 VALID
> PROCEDURE UPDT_USER VALID
> PROCEDURE SHOWALL VALID
>
> SQL> alter table t2 drop column description;
>
> Table altered.
>
> SQL> desc t2
> Name Null? Type
> --------------------------------- -------- ---------------
> ID NOT NULL NUMBER(5)
> ADDRESS NOT NULL VARCHAR2(2000)
>
> SQL> @/tmp/3
>
> OBJECT_TYPE OBJECT_NAME STATUS
> ------------------- ---------------------------------------- -------
> TABLE T1 VALID
> INDEX SYS_C005285 VALID
> INDEX SYS_C005282 VALID
> TABLE T2 VALID
> PROCEDURE UPDT_USER INVALID
> PROCEDURE SHOWALL INVALID
>
>
> .. so, I can tell now that both procedures were referencing that
t2.description
> column.
>
> If I was in your shoes, I would make an empty copy of DB,
> create plsql block that will go through all
> user_tab_columns, drop columns one after another and check
user_objects
> for INVALID. If there are some - write
> in logtable table.column and name of invalidated (.i.e referencing)
procedures.
> Than "rollback changes" - i.e. add column back and recompile objects,
> repeat for next column etc.
>
> In the end, all columns from user_tab_columns, that do not exist in
your
> logtable - unused ones.
>
> How is that?
Received on Sat Dec 18 2004 - 22:42:27 CST