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.
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!