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
this space intentionally left blank
... 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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Click Here to Expand Forum to Full Width