Toad World® Forums

Schema browser, used-by tab does not show CRUD indicators

All,

in Toad 12.9.0.11 in the used-by tab of a table, I’m trying to find out which objects will update a certain column.

Is there an easy way to do so?

Currently, I’m opening each object, search for an update of the column, continue with the next one.
It’s a slow, boring, and tedious job.

A set of CRUD indicators (or ISUD in SQL terms) would be a great help in locating the objects I’ll need to modify.

Kind regards,
Abe Kornelis.

Due to dynamic SQL, and wrapped code, it’s really tough/impossible to be able to generate a 100% accurate CRUD matrix.

But if I had the task of locating objects that update a certain column, I think I’d start with a query like the one below against DBA_SOURCE. You’ll still have to do some manual searching after that, but this should narrow it down. You could add an additional subquery with UPDATE in there if you wanted, but you get the idea.

select src.*

from dba_source src

where (src.TEXT) like ‘%DEPTNO%’ – or whatever your column is called

and (owner, name) in (select distinct owner, name

from dba_source

where upper(text) like ‘%EMP%’) – or whatever your table is called

and (owner, name) not in (select distinct owner, name

from dba_source

where line = 1

and text like lower(type) || ’ ’ || lower(name) || ’ wrapped%’);

John,

I understand that a full CRUD matrix, taking all dynamic SQL into account, is nearly impossible.
A CRUD matrix based on statis SQL only, excluding all SQL thrown at the DB by external programs would be a great start.

That said, I’ve tweaked your query and brought down the number of suspect programs to 7, which is manageable.

Thanks a lot for your query!

Happy holiday season,
Abe Kornelis