Search All Fields In All Tables For A Specific Value Oracle

Question

Is it possible to search every field of every table for a particular value in Oracle   There are hundreds of tables with thousands of rows in some tables so I know this could take a very long time to query  But the only thing I know is that a value for the field I would like to query against is 1 22 2008P09RR8   lt   I ve tried using this statement below to find an appropriate column based on what I think it should be named but it returned no results   SELECT   from dba objects  WHERE object name like   DTN     There is absolutely no documentation on this database and I have no idea where this field is being pulled from   Any thoughts

User · Answer

I was having following issues 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   Solution is   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

User · Answer

--it run 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  gt  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  gt  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

User · Answer

Here is another modified version that will compare a lower 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  gt  0 THEN       dbms output put line  t table name        t column name       match count        END IF     END LOOP  END

User · Answer

Borrowing  slightly enhancing and simplifying from this Blog post the following simple SQL statement seems to do the job quite 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

User · Answer

I did some modification to the above code to make it work faster if you are searching in only one owner  You just have to change the 3 variables v owner  v data type and v search string to fit what you are searching 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  gt  0 THEN       dbms output put line  t table name        t column name       match count        END IF     END LOOP  END

User · Answer

SELECT   from all objects WHERE object name like   your string

User · Answer

Quote      I ve tried using this statement below   to find an appropriate column based on   what I think it should be named but it   returned no results    SELECT   from dba objects WHERE object name like   DTN      A column isn t an object   If you mean that you expect the column name to be like   DTN    the query you want is   SELECT owner  table name  column name FROM all tab columns WHERE column name LIKE   DTN      But if the  DTN  string is just a guess on your part  that probably won t help   By the way  how certain are you that  1 22 2008P09RR8  is a value selected directly from a single column   If you don t know at all where it is coming from  it could be a concatenation of several columns  or the result of some function  or a value sitting in a nested table object   So you might be on a wild goose chase trying to check every column for that value   Can you not start with whatever client application is displaying this value and try to figure out what query it is using to obtain it   Anyway  diciu s answer gives one method of generating SQL queries to check every column of every table for the value   You can also do similar stuff entirely in one SQL session using a PL SQL block and dynamic SQL   Here s some hastily-written code for that       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  lt  gt   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  gt  0 THEN           dbms output put line  t table name        t column name       match count            END IF         END LOOP       END          There are some ways you could make it more efficient too   In this case  given the value you are looking for  you can clearly eliminate any column that is of NUMBER or DATE type  which would reduce the number of queries   Maybe even restrict it to columns where type is like   CHAR     Instead of one query per column  you could build one query per table like this   SELECT   FROM table1   WHERE column1    value       OR column2    value       OR column3    value

User · Answer

I know this is an old topic  But I see a comment to the question asking if it could be done in SQL rather than using PL SQL  So thought to post a solution   The below demonstration is to Search for a VALUE in all COLUMNS of all TABLES in an entire SCHEMA    Search a CHARACTER type   Let s look for the value KING in SCOTT schema   SQL gt  variable val varchar2 10  SQL gt  exec  val     KING   PL SQL procedure successfully completed   SQL gt  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 gt     Search a NUMERIC type   Let s look for the value 20 in SCOTT schema   SQL gt  variable val NUMBER SQL gt  exec  val    20  PL SQL procedure successfully completed   SQL gt  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 gt

User · Answer

Yes you can and your DBA will hate you and will find you to nail your shoes to the floor because that will cause lots of I O and bring the database performance really down as the cache purges   select column name from all tab columns c  user all tables u where c table name   u table name    for a start   I would start with the running queries  using the v session and the v sqlarea  This changes based on oracle version  This will narrow down the space and not hit everything

User · Answer

Quote      I ve tried using this statement below   to find an appropriate column based on   what I think it should be named but it   returned no results    SELECT   from dba objects WHERE object name like   DTN      A column isn t an object   If you mean that you expect the column name to be like   DTN    the query you want is   SELECT owner  table name  column name FROM all tab columns WHERE column name LIKE   DTN      But if the  DTN  string is just a guess on your part  that probably won t help   By the way  how certain are you that  1 22 2008P09RR8  is a value selected directly from a single column   If you don t know at all where it is coming from  it could be a concatenation of several columns  or the result of some function  or a value sitting in a nested table object   So you might be on a wild goose chase trying to check every column for that value   Can you not start with whatever client application is displaying this value and try to figure out what query it is using to obtain it   Anyway  diciu s answer gives one method of generating SQL queries to check every column of every table for the value   You can also do similar stuff entirely in one SQL session using a PL SQL block and dynamic SQL   Here s some hastily-written code for that       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  lt  gt   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  gt  0 THEN           dbms output put line  t table name        t column name       match count            END IF         END LOOP       END          There are some ways you could make it more efficient too   In this case  given the value you are looking for  you can clearly eliminate any column that is of NUMBER or DATE type  which would reduce the number of queries   Maybe even restrict it to columns where type is like   CHAR     Instead of one query per column  you could build one query per table like this   SELECT   FROM table1   WHERE column1    value       OR column2    value       OR column3    value

User · Answer

Borrowing  slightly enhancing and simplifying from this Blog post the following simple SQL statement seems to do the job quite 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

User · Answer

Yes you can and your DBA will hate you and will find you to nail your shoes to the floor because that will cause lots of I O and bring the database performance really down as the cache purges   select column name from all tab columns c  user all tables u where c table name   u table name    for a start   I would start with the running queries  using the v session and the v sqlarea  This changes based on oracle version  This will narrow down the space and not hit everything

User · Answer

I know this is an old topic  But I see a comment to the question asking if it could be done in SQL rather than using PL SQL  So thought to post a solution   The below demonstration is to Search for a VALUE in all COLUMNS of all TABLES in an entire SCHEMA    Search a CHARACTER type   Let s look for the value KING in SCOTT schema   SQL gt  variable val varchar2 10  SQL gt  exec  val     KING   PL SQL procedure successfully completed   SQL gt  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 gt     Search a NUMERIC type   Let s look for the value 20 in SCOTT schema   SQL gt  variable val NUMBER SQL gt  exec  val    20  PL SQL procedure successfully completed   SQL gt  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 gt

User · Answer

I don t of a simple solution on the SQL promprt  Howeve there are quite a few tools like toad and PL SQL Developer that have a GUI where a user can input the string to be searched and it will return the table procedure object where this is found

User · Answer

I don t of a simple solution on the SQL promprt  Howeve there are quite a few tools like toad and PL SQL Developer that have a GUI where a user can input the string to be searched and it will return the table procedure object where this is found

User · Answer

Yes you can and your DBA will hate you and will find you to nail your shoes to the floor because that will cause lots of I O and bring the database performance really down as the cache purges   select column name from all tab columns c  user all tables u where c table name   u table name    for a start   I would start with the running queries  using the v session and the v sqlarea  This changes based on oracle version  This will narrow down the space and not hit everything

User · Answer

SELECT   from all objects WHERE object name like   your string

User · Answer

Quote      I ve tried using this statement below   to find an appropriate column based on   what I think it should be named but it   returned no results    SELECT   from dba objects WHERE object name like   DTN      A column isn t an object   If you mean that you expect the column name to be like   DTN    the query you want is   SELECT owner  table name  column name FROM all tab columns WHERE column name LIKE   DTN      But if the  DTN  string is just a guess on your part  that probably won t help   By the way  how certain are you that  1 22 2008P09RR8  is a value selected directly from a single column   If you don t know at all where it is coming from  it could be a concatenation of several columns  or the result of some function  or a value sitting in a nested table object   So you might be on a wild goose chase trying to check every column for that value   Can you not start with whatever client application is displaying this value and try to figure out what query it is using to obtain it   Anyway  diciu s answer gives one method of generating SQL queries to check every column of every table for the value   You can also do similar stuff entirely in one SQL session using a PL SQL block and dynamic SQL   Here s some hastily-written code for that       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  lt  gt   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  gt  0 THEN           dbms output put line  t table name        t column name       match count            END IF         END LOOP       END          There are some ways you could make it more efficient too   In this case  given the value you are looking for  you can clearly eliminate any column that is of NUMBER or DATE type  which would reduce the number of queries   Maybe even restrict it to columns where type is like   CHAR     Instead of one query per column  you could build one query per table like this   SELECT   FROM table1   WHERE column1    value       OR column2    value       OR column3    value

User · Answer

I would do something like this  generates all the selects you need   You can later on feed them to sqlplus   echo  select table name from user tables     sqlplus -S user pwd   grep -v   --    grep -v  TABLE NAME    grep    A-Z     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    It yields   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 it does is - for each table name from user tables get each field  from desc  and create a select   from table where field equals  val

User · Answer

I was having following issues 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   Solution is   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

User · Answer

I modified Flood s script to execute once for each table rather than for every column of each table for faster execution  It requires Oracle 11g or greater       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  gt  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  gt  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

User · Answer

I would do something like this  generates all the selects you need   You can later on feed them to sqlplus   echo  select table name from user tables     sqlplus -S user pwd   grep -v   --    grep -v  TABLE NAME    grep    A-Z     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    It yields   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 it does is - for each table name from user tables get each field  from desc  and create a select   from table where field equals  val

User · Answer

I modified Flood s script to execute once for each table rather than for every column of each table for faster execution  It requires Oracle 11g or greater       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  gt  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  gt  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

User · Answer

Modifying the code to search case-insensitively using a 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  gt  0 THEN       dbms output put line  t table name        t column name       match count        END IF    END LOOP  END

User · Answer

I don t of a simple solution on the SQL promprt  Howeve there are quite a few tools like toad and PL SQL Developer that have a GUI where a user can input the string to be searched and it will return the table procedure object where this is found

User · Answer

I would do something like this  generates all the selects you need   You can later on feed them to sqlplus   echo  select table name from user tables     sqlplus -S user pwd   grep -v   --    grep -v  TABLE NAME    grep    A-Z     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    It yields   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 it does is - for each table name from user tables get each field  from desc  and create a select   from table where field equals  val

User · Answer

There are some free tools that make these kind of search  for example  this one works fine and source code is available  https   sites google com site freejansoft dbsearch  You ll need the Oracle ODBC driver and a DSN to use this tool

User · Answer

I would do something like this  generates all the selects you need   You can later on feed them to sqlplus   echo  select table name from user tables     sqlplus -S user pwd   grep -v   --    grep -v  TABLE NAME    grep    A-Z     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    It yields   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 it does is - for each table name from user tables get each field  from desc  and create a select   from table where field equals  val

User · Answer

Quote      I ve tried using this statement below   to find an appropriate column based on   what I think it should be named but it   returned no results    SELECT   from dba objects WHERE object name like   DTN      A column isn t an object   If you mean that you expect the column name to be like   DTN    the query you want is   SELECT owner  table name  column name FROM all tab columns WHERE column name LIKE   DTN      But if the  DTN  string is just a guess on your part  that probably won t help   By the way  how certain are you that  1 22 2008P09RR8  is a value selected directly from a single column   If you don t know at all where it is coming from  it could be a concatenation of several columns  or the result of some function  or a value sitting in a nested table object   So you might be on a wild goose chase trying to check every column for that value   Can you not start with whatever client application is displaying this value and try to figure out what query it is using to obtain it   Anyway  diciu s answer gives one method of generating SQL queries to check every column of every table for the value   You can also do similar stuff entirely in one SQL session using a PL SQL block and dynamic SQL   Here s some hastily-written code for that       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  lt  gt   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  gt  0 THEN           dbms output put line  t table name        t column name       match count            END IF         END LOOP       END          There are some ways you could make it more efficient too   In this case  given the value you are looking for  you can clearly eliminate any column that is of NUMBER or DATE type  which would reduce the number of queries   Maybe even restrict it to columns where type is like   CHAR     Instead of one query per column  you could build one query per table like this   SELECT   FROM table1   WHERE column1    value       OR column2    value       OR column3    value

User · Answer

--it run 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  gt  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  gt  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

User · Answer

There are some free tools that make these kind of search  for example  this one works fine and source code is available  https   sites google com site freejansoft dbsearch  You ll need the Oracle ODBC driver and a DSN to use this tool

User · Answer

I don t of a simple solution on the SQL promprt  Howeve there are quite a few tools like toad and PL SQL Developer that have a GUI where a user can input the string to be searched and it will return the table procedure object where this is found

User · Answer

if we know the table and colum names but want to find out the number of times string is appearing 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  lt  gt  0 then dbms output put line rec2 owner       rec3 ct   else null  end if  end loop  end

User · Answer

I did some modification to the above code to make it work faster if you are searching in only one owner  You just have to change the 3 variables v owner  v data type and v search string to fit what you are searching 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  gt  0 THEN       dbms output put line  t table name        t column name       match count        END IF     END LOOP  END

User · Answer

Yes you can and your DBA will hate you and will find you to nail your shoes to the floor because that will cause lots of I O and bring the database performance really down as the cache purges   select column name from all tab columns c  user all tables u where c table name   u table name    for a start   I would start with the running queries  using the v session and the v sqlarea  This changes based on oracle version  This will narrow down the space and not hit everything

User · Answer

Modifying the code to search case-insensitively using a 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  gt  0 THEN       dbms output put line  t table name        t column name       match count        END IF    END LOOP  END

User · Answer

if we know the table and colum names but want to find out the number of times string is appearing 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  lt  gt  0 then dbms output put line rec2 owner       rec3 ct   else null  end if  end loop  end

User · Answer

Procedure to Search 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  gt  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        Execute Statement    DECLARE     SEARCH STR VARCHAR2 200       TAB COL RECS VARCHAR2 200       BEGIN       SEARCH STR    10        SEARCH DB          SEARCH STR   gt  SEARCH STR          TAB COL RECS   gt  TAB COL RECS               DBMS OUTPUT PUT LINE  TAB COL RECS        TAB COL RECS        END    Sample Results  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

User · Answer

Here is another modified version that will compare a lower 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  gt  0 THEN       dbms output put line  t table name        t column name       match count        END IF     END LOOP  END

User · Answer

Procedure to Search 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  gt  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        Execute Statement    DECLARE     SEARCH STR VARCHAR2 200       TAB COL RECS VARCHAR2 200       BEGIN       SEARCH STR    10        SEARCH DB          SEARCH STR   gt  SEARCH STR          TAB COL RECS   gt  TAB COL RECS               DBMS OUTPUT PUT LINE  TAB COL RECS        TAB COL RECS        END    Sample Results  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

[sql] Search All Fields In All Tables For A Specific Value (Oracle)

Examples related to sql

Examples related to oracle

Examples related to search

Examples related to plsql

Examples related to database-table