Re: find columns used in procedures

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 - 19:10:48 CST