Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Return all tables with a specific value
- Thread starter amberlynn
- Start date
SantaMufasa
Technical User
Yes, AmberLynn, it is certainly possible, but imagine what it would take -- you would need either a SQL or PL/SQL script that queries...
each value
in each column
in each row
in each table
in each owner/schema...
in the entire Oracle database.
Luckily, Oracle is very well suited for doing such a thing (compared to other non-Oracle database engines). Running such a query might take hours (or days) depending upon the size of your database.
You or I could write such a script, but if I were to assist, I would need to ask several additional specification questions.
Can you offer some additional business justification for undertaking such a task?
![[santa]](/data/assets/smilies/santa.gif "[santa] [santa]")
Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
each value
in each column
in each row
in each table
in each owner/schema...
in the entire Oracle database.
Luckily, Oracle is very well suited for doing such a thing (compared to other non-Oracle database engines). Running such a query might take hours (or days) depending upon the size of your database.
You or I could write such a script, but if I were to assist, I would need to ask several additional specification questions.
Can you offer some additional business justification for undertaking such a task?
Mufasa(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
- Thread starter
- #3
Our company is using a custom developed app with Oracle as the backend - the company name is spelled wrong, and I've already wasted a lot of time going through table after table trying to find this error.
The database size is not huge, so I can't see it taking too much time (but what do I know
)
The database size is not huge, so I can't see it taking too much time (but what do I know
)
SantaMufasa
Technical User
Ah, yes. Now I understand. Your specific need simplifies things enormously since you do not need to look at values in DATE and NUMBER columns, for example.
Must you look in CLOB (Character Large OBject) columns, or can we limit the search to just VARCHAR and CHAR columns?
Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
Must you look in CLOB (Character Large OBject) columns, or can we limit the search to just VARCHAR and CHAR columns?
Mufasa(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
SantaMufasa
Technical User
Also, it seems to me that the COMPANY NAME is likely to appear in only certain columns of certain tables, as well, right?
Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
Mufasa(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
- Thread starter
- #6
SantaMufasa
Technical User
Amberlynn said:
My thought is that "company name" will not appear in any row of a column named "CITY", "STATE", "ZIP", or "PHONE", right? Would could simplify our search-and-update routine by identifying the only columns in which the incorrectly spelled company name could reside.Amberlynn said:
Mufasa(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
- Thread starter
- #8
Here's something to start with. Change the DBA9i to your company name, and change the owner to appropriate schema.
This produces the list of tables with columns that have chars. Then copy and paste some of those outputs to see if it finds your company name. You mentioned, you will be dealing with mispelled words, so edit accordingly.
select 'select distinct '||C.column_name||' from '||c.owner||'.'||c.table_name||' where '||c.column_name||' like ''%DBA9i%''
and rowid in ( select distinct rowid from '||c.owner||'.'||c.TABLE_NAME||' where '||c.COLUMN_NAME||' like ''%DBA9i%'');'
from dba_tables t,dba_tab_cols c where t.table_name=c.table_name and data_type like '%CHAR%' and t.owner = 'RMAN'
--not in ('OLAPSYS','OUTLN','SYS','SYSTEM')
/
This produces the list of tables with columns that have chars. Then copy and paste some of those outputs to see if it finds your company name. You mentioned, you will be dealing with mispelled words, so edit accordingly.
select 'select distinct '||C.column_name||' from '||c.owner||'.'||c.table_name||' where '||c.column_name||' like ''%DBA9i%''
and rowid in ( select distinct rowid from '||c.owner||'.'||c.TABLE_NAME||' where '||c.COLUMN_NAME||' like ''%DBA9i%'');'
from dba_tables t,dba_tab_cols c where t.table_name=c.table_name and data_type like '%CHAR%' and t.owner = 'RMAN'
--not in ('OLAPSYS','OUTLN','SYS','SYSTEM')
/
- Status
Similar threads
