Following is syntax for searching all views for a given field/column name. In the example below, we're searching for OU_UID - make sure to replace this with the appropriate field/column name. This will return a list of all views where containing a field/column name of OU_UID:
SELECT table_name FROM all_tab_columns where column_name like '%OU_UID' order by table_name asc
To find tables or views in your database that contain column names that contain "DATA_SENSTIVE_EO_IND" or "DATA_SENSITIVE_IND" use the following query:
SELECT table_name, column_name FROM all_tab_columns where column_name like '%DATA_SENSITIVE%' and column_name like '%IND%' AND table_name not like'%UVW%' and table_name not like '%DVW%' and table_name not like '%HVW%' and table_name not like '%ELEMENT_NATIONAL%' order by table_name asc
On the other hand, you may have a view which references a given field/column, but names it differently, in which case the query above would not find it. The following query returns the view name and view SQL script for all views referencing a given table (ELEMENT_SUBNATIONAL). This is particularly helpful if the same field name is found in multiple tables.
- Run the following query in Query Builder (required due to privileges) and output the results to HTML.
- Search (CTRL+F) the HTML results for the specific table.field (i.e. ELEMENT_SUBNATIONAL.ELCODE_BCD):
select view_name, text from all_views where owner in ('BIOTICS_USER') and view_name in (select name from all_dependencies WHERE type = 'VIEW' AND referenced_owner = 'BIOTICS_USER' AND referenced_name = 'ELEMENT_SUBNATIONAL')