Find Column Dependencies
I am having to pull out all the dependencies for a column as below.
When I select a column in a table, it should list
1. All Views that use this column and table
2. All Stored procs that use this column and table
3. All Functions that use this column and table
4. All Pl/SQL scripts that use this column and table.
Is there a easy way of getting this information from the Oracle dba tables?
No. While DBA_TAB_COLUMNS shows what columns is in what view it is going to be difficult to see how do you know where the column came from? You can grep through the DDL for the view and stored procedures, but converting it into a view is going to be difficult. Especially since you can do dynamic SQL in stored procedures and you can alias, concatenate and rename columns.
Originally Posted by Orako
... not to mention remote code hitting the database via a DBLink, code embedded in a front-end application, ad-hoc queries as well as shell scripts executing sql code.