Searching for string in the whole schema

Question

I have Oracle database with many schemas. I'd like to find xyz string in all the tables in one specific schema. I've tried to do it as in accepted answer here (Search All Fields In All Tables For A Specific Value (Oracle)). Unfortunately, I get error:

3 ORA-00903: invalid table name
ORA-06512: at line 8 SQL.sql 30 2

where line 8 is

SELECT COUNT(*) FROM ' || t.owner || '.' || t.table_name ||

How can I do this full search? I've also tried other googled solutions but none of them worked for me.

Are you running the entire SQL posted in that other answer, or are you trying to run just the EXECUTE IMMEDIATE bit? (it wont work if you do that - this sql from the other answer is "an sql that writes an sql" - you need to run all of it otherwise it'll fail) — Caius Jard, Dec 05 '17 at 11:28

Caius Jard · Accepted Answer · 2017-12-05T13:27:51.703

2

Adapted from the other answer, run this query:

SELECT
    'SELECT '''|| owner||table_name||column_name|| ''' as locn, COUNT(*) FROM "' || owner || '"."' || table_name ||'" WHERE "'||column_name||'" = ''[VALUE YOURE LOOKING FOR HERE]'' UNION ALL'
FROM
  all_tab_columns
WHERE owner <> 'SYS' and data_type LIKE '%CHAR%'

Replace the [VALUE YOURE LOOKING FOR HERE] with the value you're looking for. Replace the square brackets too. Do not touch any apostrophes.

Then run the query, and it'll produce a huge number of sql statements. Copy them out of your query tool results grid, paste them into the query area panel, delete the last union all and then run them. And wait. For a very long time. Eventually it'll produce a list of every table and column name, together with the count of the number of times that value you're looking for appears in that column

edited Dec 05 '17 at 13:27

answered Dec 05 '17 at 11:31

Caius Jard

72,509
5
49
80

2

This will fail for case sensitive object identifiers such as a table or a column created as `CREATE TABLE schema."TaBlE_NaMe" ( "id" NUMBER )`. You would need to wrap all the identifiers in double quotes. – MT0 Dec 05 '17 at 11:39
1

Ugh.. Perverts that create case sensitive oracle column names should be fed into a shredder. Added some " :) – Caius Jard Dec 05 '17 at 11:41
I get error: 4 ORA-00904: "T"."COLUMN_NAME": invalid identifier SQL1.sql 60 142 (here: t.column_name || '" = ''ShName'' UNION ALL') – user1809566 Dec 05 '17 at 11:44
And by the way, I'm looking for string in the value, not column name. As output I want name of the table and column in which column the value can be found – user1809566 Dec 05 '17 at 11:49
@user1809566 - the code is missing an alias for the table name: `... FROM all_tab_columns t WHERE ...` – Alex Poole Dec 05 '17 at 11:57
@user1809566 Or you can remove the `t.` prefixes as the alias `t` has not been defined (and is not necessary as you are only referencing a single dictionary table). – MT0 Dec 05 '17 at 12:00
@user1809566 this doesn't look for the value in the column name, it generates a lot of sql strings that each look for the value in the row data of each column, eg `SELECT count(*) FROM table WHERE col1 = 'hello' UNION ALL SELECT count(*) FROM table WHERE col2 = 'hello' ` and so on. PS, i fixed the syntax error in the query, apologies - copy and run it again – Caius Jard Dec 05 '17 at 13:22
You just need to wrap your mind round the fact that "this is an SQL that writes SQL as its output" you then need to take that SQL that is output, paste it back into the query pane of your SQL running tool, and run it. It's not "code that looks for data", it's "code that writes code that looks for data" – Caius Jard Dec 05 '17 at 13:24

score 0 · Answer 2 · answered Dec 05 '17 at 13:47

You can adapt the original code to cope with tables (and schemas, and columns) created with case-sensitive or otherwise invalid object identifiers, by treating everything as a quoted identifier.

EXECUTE IMMEDIATE
  'SELECT COUNT(*) FROM "' || t.owner || '"."' || t.table_name || '"' ||
  ' WHERE "'||t.column_name||'" = :1'
  INTO match_count
  USING '1/22/2008P09RR8';

but using whatever string you're actually looking for, of course.

In the dynamic SQL that generates, the owner, table name and column name are now all enclosed in double quotes - which is what @CaiusJard is doing, but this still executes separate queries inside an anonymous block.

Searching for string in the whole schema

2 Answers2