How to find columns in a table + stored procedures that depend on them?
Scenario:
I need to list all the columns in table1
and all the stored procedures that depend on those columns of this table1
. I need to populate the column name and stored procedures in a new table.
I created new_table(col1, sProc)
and tried to populate the column name and the corresponding stored procedure on this one new_table
. The code I wrote is below:
Declare
Begin
for i in (select column_name from user_tab_columns where lower(table_name) like 'table1') loop
insert into new_table
select i.column_name,
name
from user_source
where lower(text) like '%' || i.column_name || '%';
commit;
end loop;
end;
Result: The scripts run successfully, but the data is not populated on this new_table.
Stress: I tried to solve it for one whole day and couldn't figure it out. Any help on this would be greatly appreciated. Thanks again.
source to share
The best you can do is provide the package name (as is the value in the field USER_SOURCE.NAME
) along with the column. As rexem points out in his comment, you don't need to resort to a for loop:
INSERT INTO new_table (col1, sproc)
SELECT i.column_name, u.name
FROM user_tab_columns i,
user_source u
WHERE lower(i.table_name) like 'table1'
AND lower(u.text) like '%' || lower(i.column_name) || '%';
source to share
You can reduce false positives by including USER_DEPENDENCIES
in your request. You can limit your search to types (or alternatively include TYPE
in NEW_TABLE
).
insert into new_table (col1, sproc)
select distinct tc.column_name, sp.name
from user_tab_columns tc
, user_source sp
, user_dependencies d
where d.referenced_name = 'TABLE1'
and d.referenced_type = 'TABLE'
and d.type IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION'
, 'PROCEDURE', 'TYPE', 'TRIGGER')
and tc.table_name = 'TABLE1'
and sp.name = d.name
and instr(lower(sp.text), lower(tc.column_name)) > 0
/
source to share