Wednesday, July 28, 2010

How to search for column reference in oracle

Imagine that you need to find out which code update a column in a table. Some developer tools could point out who refers to the table but so far I couldn't find the one that could answer my need.

All_source comes to the rescue, I'd say that it is a special view that holds all the database objects accessible by the user.

Here is the description on the view's columns:
OWNER VARCHAR2(30) NOT NULL Owner of the object
NAME VARCHAR2(30) NOT NULL Name of the object
TYPE VARCHAR2(12)
Type of object: FUNCTION, JAVA SOURCE, PACKAGE, PACKAGE BODY, PROCEDURE, TRIGGER, TYPE, TYPE BODY
LINE NUMBER NOT NULL Line number of this line of source
TEXT VARCHAR2(4000)
Text source of the stored object

Example:
I need to find out who refers to column FK_USER.

select * from all_source where text like '%FK_USER%'

And I will get all the places which have reference to FK_USER.

If you would take it further, you could utilize it for searching almost anything (e.g. comments in the code, TODO tag, exception).

... wondering if there're more "magical" view like this.

No comments: