Search all fields in all tables for a specific value (Oracle)

Question

Is it possible to search each field of each table for a specific value in Oracle?

Some tables have hundreds of tables with thousands of rows, so I know this can take a very long time. But the only thing I know is that the value for the field I would like to query is 1/22/2008P09RR8 . <

I tried using this statement below to find the corresponding column based on the fact that, in my opinion, it should be named, but did not give any results.

 SELECT * from dba_objects WHERE object_name like '%DTN%' 

There is no documentation in this database, and I have no idea where this field is extracted from.

Any thoughts?

Answer 1

Quote:

I tried using this statement below to find the corresponding column based on what I think it should be called, but it did not get any results. *

 SELECT * from dba_objects WHERE object_name like '%DTN%' 

The column is not an object. If you mean that you expect the column name to be like "% DTN%", the query you want is the following:

 SELECT owner, table_name, column_name FROM all_tab_columns WHERE column_name LIKE '%DTN%'; 

But if the string "DTN" is just a hunch on your part, it probably won't help.

By the way, how confident are you that "1/22 / 2008P09RR8" is a value directly selected from a single column? If you don’t know where it comes from at all, it can be a concatenation of several columns or the result of some function or a value located in a nested table object. Thus, you may end up on a wild goose hunt, trying to check each column for this value. Could you start with which client application displays this value and try to figure out which request it uses to receive it?

In any case, diciu's answer provides one method for generating SQL queries to check each column of each table for a value. You can also do similar things completely in one SQL session using PL / SQL block and dynamic SQL. Here is some hastily written code for this:

  SET SERVEROUTPUT ON SIZE 100000 DECLARE match_count INTEGER; BEGIN FOR t IN (SELECT owner, table_name, column_name FROM all_tab_columns WHERE owner <> 'SYS' and data_type LIKE '%CHAR%') LOOP EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || t.owner || '.' || t.table_name || ' WHERE '||t.column_name||' = :1' INTO match_count USING '1/22/2008P09RR8'; IF match_count > 0 THEN dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count ); END IF; END LOOP; END; / 

There are several ways to make it more effective.

In this case, given the value you are looking for, you can clearly exclude any column of type NUMBER or DATE, which will reduce the number of queries. Perhaps even limit it to columns where the type is like "% CHAR%".

Instead of a single query per column, you can build one query for a table as follows:

 SELECT * FROM table1 WHERE column1 = 'value' OR column2 = 'value' OR column3 = 'value' ... ; 

Answer 2

I made some changes to the above code to make it work faster if you are looking for only one owner. You just need to change the 3 variables v_owner, v_data_type and v_search_string according to what you are looking for.

 SET SERVEROUTPUT ON SIZE 100000 DECLARE match_count INTEGER; -- Type the owner of the tables you are looking at v_owner VARCHAR2(255) :='ENTER_USERNAME_HERE'; -- Type the data type you are look at (in CAPITAL) -- VARCHAR2, NUMBER, etc. v_data_type VARCHAR2(255) :='VARCHAR2'; -- Type the string you are looking at v_search_string VARCHAR2(4000) :='string to search here...'; BEGIN FOR t IN (SELECT table_name, column_name FROM all_tab_cols where owner=v_owner and data_type = v_data_type) LOOP EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||t.table_name||' WHERE '||t.column_name||' = :1' INTO match_count USING v_search_string; IF match_count > 0 THEN dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count ); END IF; END LOOP; END; / 

Answer 3

Yes, you can, and your database administrator will hate you, and you will find that you are nailing the shoes because it will cause a lot of I / O and will decrease database performance as the cache is cleared.

 select column_name from all_tab_columns c, user_all_tables u where c.table_name = u.table_name; 

to start.

I would start with running queries using v$session and v$sqlarea . This varies depending on the version of oracle. This will reduce space and not hit everything.

Answer 4

I know this is an old topic. But I see a comment on the question of whether this can be done in SQL , and not when using PL/SQL . So we thought to publish a solution.

The following shows Look for VALUE in all columns of all tables in all SCHEMA :

• Find type CHARACTER

Look at the KING value in the SCOTT schema.

 SQL> variable val varchar2(10) SQL> exec :val := 'KING' PL/SQL procedure successfully completed. SQL> SELECT DISTINCT SUBSTR (:val, 1, 11) "Searchword", 2 SUBSTR (table_name, 1, 14) "Table", 3 SUBSTR (column_name, 1, 14) "Column" 4 FROM cols, 5 TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select ' 6 || column_name 7 || ' from ' 8 || table_name 9 || ' where upper(' 10 || column_name 11 || ') like upper(''%' 12 || :val 13 || '%'')' ).extract ('ROWSET/ROW/*') ) ) t 14 ORDER BY "Table" 15 / Searchword Table Column ----------- -------------- -------------- KING EMP ENAME SQL> 

• Find the NUMBER type

Look at the value 20 in the SCOTT diagram.

 SQL> variable val NUMBER SQL> exec :val := 20 PL/SQL procedure successfully completed. SQL> SELECT DISTINCT SUBSTR (:val, 1, 11) "Searchword", 2 SUBSTR (table_name, 1, 14) "Table", 3 SUBSTR (column_name, 1, 14) "Column" 4 FROM cols, 5 TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select ' 6 || column_name 7 || ' from ' 8 || table_name 9 || ' where upper(' 10 || column_name 11 || ') like upper(''%' 12 || :val 13 || '%'')' ).extract ('ROWSET/ROW/*') ) ) t 14 ORDER BY "Table" 15 / Searchword Table Column ----------- -------------- -------------- 20 DEPT DEPTNO 20 EMP DEPTNO 20 EMP HIREDATE 20 SALGRADE HISAL 20 SALGRADE LOSAL SQL> 

Answer 5

I would do something like this (generates everything you need). You can deliver them later to sqlplus:

 echo "select table_name from user_tables;" | sqlplus -S user/pwd | grep -v "^--" | grep -v "TABLE_NAME" | grep "^[AZ]" | while read sw; do echo "desc $sw" | sqlplus -S user/pwd | grep -v "\-\-\-\-\-\-" | awk -F' ' '{print $1}' | while read nw; do echo "select * from $sw where $nw='val'"; done; done; 

This gives:

 select * from TBL1 where DESCRIPTION='val' select * from TBL1 where ='val' select * from TBL2 where Name='val' select * from TBL2 where LNG_ID='val' 

And what does he do - for each table_name from user_tables get each field (from desc) and create select * from the table, where the field is equal to 'val'.

Answer 6

Here is another modified version that will compare a smaller substring match. This works in Oracle 11g.

 DECLARE match_count INTEGER; -- Type the owner of the tables you are looking at v_owner VARCHAR2(255) :='OWNER_NAME'; -- Type the data type you are look at (in CAPITAL) -- VARCHAR2, NUMBER, etc. v_data_type VARCHAR2(255) :='VARCHAR2'; -- Type the string you are looking at v_search_string VARCHAR2(4000) :='%lower-search-sub-string%'; BEGIN FOR t IN (SELECT table_name, column_name FROM all_tab_cols where owner=v_owner and data_type = v_data_type) LOOP EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||t.table_name||' WHERE lower('||t.column_name||') like :1' INTO match_count USING v_search_string; IF match_count > 0 THEN dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count ); END IF; END LOOP; END; / 

Answer 7

if we know the names of the tables and colum, but want to know the number of rows that appear for each schema:

 Declare owner VARCHAR2(1000); tbl VARCHAR2(1000); cnt number; ct number; str_sql varchar2(1000); reason varchar2(1000); x varchar2(1000):='%string_to_be_searched%'; cursor csr is select owner,table_name from all_tables where table_name ='table_name'; type rec1 is record ( ct VARCHAR2(1000)); type rec is record ( owner VARCHAR2(1000):='', table_name VARCHAR2(1000):=''); rec2 rec; rec3 rec1; begin for rec2 in csr loop --str_sql:= 'select count(*) from '||rec.owner||'.'||rec.table_name||' where CTV_REMARKS like '||chr(39)||x||chr(39); --dbms_output.put_line(str_sql); --execute immediate str_sql execute immediate 'select count(*) from '||rec2.owner||'.'||rec2.table_name||' where column_name like '||chr(39)||x||chr(39) into rec3; if rec3.ct <> 0 then dbms_output.put_line(rec2.owner||','||rec3.ct); else null; end if; end loop; end; 

Answer 8

I modified the Flood script to execute once for each table, and not for each column of each table for faster execution. This requires Oracle 11g or more.

  set serveroutput on size 100000 declare v_match_count integer; v_counter integer; -- The owner of the tables to search through (case-sensitive) v_owner varchar2(255) := 'OWNER_NAME'; -- A string that is part of the data type(s) of the columns to search through (case-insensitive) v_data_type varchar2(255) := 'CHAR'; -- The string to be searched for (case-insensitive) v_search_string varchar2(4000) := 'FIND_ME'; -- Store the SQL to execute for each table in a CLOB to get around the 32767 byte max size for a VARCHAR2 in PL/SQL v_sql clob := ''; begin for cur_tables in (select owner, table_name from all_tables where owner = v_owner and table_name in (select table_name from all_tab_columns where owner = all_tables.owner and data_type like '%' || upper(v_data_type) || '%') order by table_name) loop v_counter := 0; v_sql := ''; for cur_columns in (select column_name from all_tab_columns where owner = v_owner and table_name = cur_tables.table_name and data_type like '%' || upper(v_data_type) || '%') loop if v_counter > 0 then v_sql := v_sql || ' or '; end if; v_sql := v_sql || 'upper(' || cur_columns.column_name || ') like ''%' || upper(v_search_string) || '%'''; v_counter := v_counter + 1; end loop; v_sql := 'select count(*) from ' || cur_tables.table_name || ' where ' || v_sql; execute immediate v_sql into v_match_count; if v_match_count > 0 then dbms_output.put_line('Match in ' || cur_tables.owner || ': ' || cur_tables.table_name || ' - ' || v_match_count || ' records'); end if; end loop; exception when others then dbms_output.put_line('Error when executing the following: ' || dbms_lob.substr(v_sql, 32600)); end; / 

Answer 9

Search procedure for the entire database:

  CREATE or REPLACE PROCEDURE SEARCH_DB(SEARCH_STR IN VARCHAR2, TAB_COL_RECS OUT VARCHAR2) IS match_count integer; qry_str varchar2(1000); CURSOR TAB_COL_CURSOR IS SELECT TABLE_NAME,COLUMN_NAME,OWNER,DATA_TYPE FROM ALL_TAB_COLUMNS WHERE DATA_TYPE in ('NUMBER','VARCHAR2') AND OWNER='SCOTT'; BEGIN FOR TAB_COL_REC IN TAB_COL_CURSOR LOOP qry_str := 'SELECT COUNT(*) FROM '||TAB_COL_REC.OWNER||'.'||TAB_COL_REC.TABLE_NAME|| ' WHERE '||TAB_COL_REC.COLUMN_NAME; IF TAB_COL_REC.DATA_TYPE = 'NUMBER' THEN qry_str := qry_str||'='||SEARCH_STR; ELSE qry_str := qry_str||' like '||SEARCH_STR; END IF; --dbms_output.put_line( qry_str ); EXECUTE IMMEDIATE qry_str INTO match_count; IF match_count > 0 THEN dbms_output.put_line( qry_str ); --dbms_output.put_line( TAB_COL_REC.TABLE_NAME ||' '||TAB_COL_REC.COLUMN_NAME ||' '||match_count); TAB_COL_RECS := TAB_COL_RECS||'@@'||TAB_COL_REC.TABLE_NAME||'##'||TAB_COL_REC.COLUMN_NAME; END IF; END LOOP; END SEARCH_DB; 

Run expression

  DECLARE SEARCH_STR VARCHAR2(200); TAB_COL_RECS VARCHAR2(200); BEGIN SEARCH_STR := 10; SEARCH_DB( SEARCH_STR => SEARCH_STR, TAB_COL_RECS => TAB_COL_RECS ); DBMS_OUTPUT.PUT_LINE('TAB_COL_RECS = ' || TAB_COL_RECS); END; 

Results Examples

 Connecting to the database test. SELECT COUNT(*) FROM SCOTT.EMP WHERE DEPTNO=10 SELECT COUNT(*) FROM SCOTT.DEPT WHERE DEPTNO=10 TAB_COL_RECS = @@EMP##DEPTNO@@DEPT##DEPTNO Process exited. Disconnecting from the database test. 

Answer 10

I do not have a simple solution in SQL promprt. However, there are quite a few tools such as toad and PL / SQL Developer, which have a graphical interface in which the user can enter a string to search, and it will return the table / procedure / object where it will be found.

Answer 11

There are several free tools that do this search, for example, this file works fine, and the source code is available: https://sites.google.com/site/freejansoft/dbsearch

To use this tool, you need the Oracle ODBC driver and DSN.

Answer 12

- it is completed - no error

  SET SERVEROUTPUT ON SIZE 100000 DECLARE v_match_count INTEGER; v_counter INTEGER; v_owner VARCHAR2 (255) := 'VASOA'; v_search_string VARCHAR2 (4000) := '99999'; v_data_type VARCHAR2 (255) := 'CHAR'; v_sql CLOB := ''; BEGIN FOR cur_tables IN ( SELECT owner, table_name FROM all_tables WHERE owner = v_owner AND table_name IN (SELECT table_name FROM all_tab_columns WHERE owner = all_tables.owner AND data_type LIKE '%' || UPPER (v_data_type) || '%') ORDER BY table_name) LOOP v_counter := 0; v_sql := ''; FOR cur_columns IN (SELECT column_name, table_name FROM all_tab_columns WHERE owner = v_owner AND table_name = cur_tables.table_name AND data_type LIKE '%' || UPPER (v_data_type) || '%') LOOP IF v_counter > 0 THEN v_sql := v_sql || ' or '; END IF; IF cur_columns.column_name is not null THEN v_sql := v_sql || 'upper(' || cur_columns.column_name || ') =''' || UPPER (v_search_string)||''''; v_counter := v_counter + 1; END IF; END LOOP; IF v_sql is null THEN v_sql := 'select count(*) from ' || v_owner || '.' || cur_tables.table_name; END IF; IF v_sql is not null THEN v_sql := 'select count(*) from ' || v_owner || '.' || cur_tables.table_name || ' where ' || v_sql; END IF; --v_sql := 'select count(*) from ' ||v_owner||'.'|| cur_tables.table_name ||' where '|| v_sql; --dbms_output.put_line(v_sql); --DBMS_OUTPUT.put_line (v_sql); EXECUTE IMMEDIATE v_sql INTO v_match_count; IF v_match_count > 0 THEN DBMS_OUTPUT.put_line (v_sql); dbms_output.put_line('Match in ' || cur_tables.owner || ': ' || cur_tables.table_name || ' - ' || v_match_count || ' records'); END IF; END LOOP; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line ( 'Error when executing the following: ' || DBMS_LOB.SUBSTR (v_sql, 32600)); END; / 

Answer 13

Changing the case-insensitive search code using the LIKE query instead of finding exact matches ...

 DECLARE match_count INTEGER; -- Type the owner of the tables you want to search. v_owner VARCHAR2(255) :='USER'; -- Type the data type you're looking for (in CAPS). Examples include: VARCHAR2, NUMBER, etc. v_data_type VARCHAR2(255) :='VARCHAR2'; -- Type the string you are looking for. v_search_string VARCHAR2(4000) :='Test'; BEGIN dbms_output.put_line( 'Starting the search...' ); FOR t IN (SELECT table_name, column_name FROM all_tab_cols where owner=v_owner and data_type = v_data_type) LOOP EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||t.table_name||' WHERE LOWER('||t.column_name||') LIKE :1' INTO match_count USING LOWER('%'||v_search_string||'%'); IF match_count > 0 THEN dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count ); END IF; END LOOP; END; 

Answer 14

Borrowing to slightly enhance and simplify this blog post , the following simple SQL statement seems to do the job well:

 SELECT DISTINCT (:val) "Search Value", TABLE_NAME "Table", COLUMN_NAME "Column" FROM cols, TABLE (XMLSEQUENCE (DBMS_XMLGEN.GETXMLTYPE( 'SELECT "' || COLUMN_NAME || '" FROM "' || TABLE_NAME || '" WHERE UPPER("' || COLUMN_NAME || '") LIKE UPPER(''%' || :val || '%'')' ).EXTRACT ('ROWSET/ROW/*'))) ORDER BY "Table"; 

Answer 15

I had the following problems for @Lalit Kumars answer,

 ORA-19202: Error occurred in XML processing ORA-00904: "SUCCESS": invalid identifier ORA-06512: at "SYS.DBMS_XMLGEN", line 288 ORA-06512: at line 1 19202. 00000 - "Error occurred in XML processing%s" *Cause: An error occurred when processing the XML function *Action: Check the given error message and fix the appropriate problem 

Decision:

 WITH char_cols AS (SELECT /*+materialize */ table_name, column_name FROM cols WHERE data_type IN ('CHAR', 'VARCHAR2')) SELECT DISTINCT SUBSTR (:val, 1, 11) "Searchword", SUBSTR (table_name, 1, 14) "Table", SUBSTR (column_name, 1, 14) "Column" FROM char_cols, TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select "' || column_name || '" from "' || table_name || '" where upper("' || column_name || '") like upper(''%' || :val || '%'')' ).extract ('ROWSET/ROW/*') ) ) t ORDER BY "Table" /