As an ETL developer, this is something I have to do when developers of a source database application have to extend the length of a field. Because that field can be used in so many different projects and/or datamart, I need to find all the possible tables and views of all schemas having the field in my database.
Here is how I do it.. Suppose i’m looking for a column named “awesome_id”. All I have to do is to execute this query with a user having the privileges to read into all schemas.
SELECT * FROM ALL_TAB_COLUMNS WHERE COLUMN_NAME LIKE '%AWESOME_ID%';
It will return all the tables having a column with the word “awesome_id”.
But it didn’t look the queries used to build views. Instead I have to use:
CREATE TABLE search_into_views (owner_name varchar2(150), view_name varchar2(150) null, t clob null);
INSERT INTO search_into_views
SELECT owner, view_name ,to_lob(text) FROM all_views;
SELECT owner_name, view_name, CAST(SUBSTR(t,1,4000) as VARCHAR2(4000)) as q
FROM search_into_views WHERE UPPER(t) LIKE '%AWESOME_ID%';
DROP TABLE search_into_views;
Unfortunately, it only returns the first 4000 characters of the queries, but the lookup is done on the complete queries.