This discussion is archived
4 Replies Latest reply: Jan 20, 2013 11:35 PM by BillyVerreynne RSS

findout table name and column name

874273 Newbie
Currently Being Moderated
Hi Masters,

I have a task to find out a table name and column name when I pass a column value into a parameter in my procedure. I have written the query like this...
But it is taking very long time. I need to search entire my database. Can anybody help me in this regard. It is very nice of you.

select table_name, column_name 
      from (select rownum,table_name, column_name, 
                   dbms_xmlgen.getxml('select 1 from "'||table_name||'" where "'||column_name||'" =
''&val''') x 
              from user_tab_columns where data_type='VARCHAR2'
    ) where length(x)!=0;
Oracle version
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
Please help me...!!

Regards
SA
  • 1. Re: findout table name and column name
    sb92075 Guru
    Currently Being Moderated
    HOW To Make TUNING request
    SQL and PL/SQL FAQ
  • 2. Re: findout table name and column name
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    871270 wrote:

    But it is taking very long time. I need to search entire my database.
    Exactly. As this approach needs to scan the entire database. And as this approach is not how one uses databases!

    So why exactly are you using this approach? How on earth it this a sensible and logical approach to relational design and data modelling?
  • 3. Re: findout table name and column name
    874273 Newbie
    Currently Being Moderated
    I have tried like this also.....!! But result is same

    DECLARE
      l_value VARCHAR2(100) := ' VALUE ' ;
      l_cnt   PLS_INTEGER;
      l_sql    VARCHAR2(1000);
    BEGIN
      FOR x IN (
          SELECT table_name, column_name
            FROM user_tab_cols
           WHERE user = 'MY SCHEMA NAME'
             AND data_type IN ('CHAR', 'VARCHAR2' )
      )
      LOOP
        l_sql := 'SELECT COUNT(*) FROM ' || user || '.' || x.table_name || ' WHERE ' || x.column_name || ' = :1';
        EXECUTE IMMEDIATE l_sql
          INTO l_cnt
         USING l_value;
        IF( l_cnt > 0 )
        THEN
          dbms_output.put_line( 'Found the value ' || l_value || ' in owner = ' || USER || ' table name = ' || x.table_name || ' column name = ' || x.column_name );
        END IF;
      END LOOP;
    END;
    Regads
    SA
  • 4. Re: findout table name and column name
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    You have not answered the question as to WHY this needs to be done? What are the business requirements behind this (seemingly insane) approach?

    As for the approach. It WILL be slow. ALWAYS.

    Why?

    It hits an entire table looking for relevant rows. So if there are a 100 tables, it means at minimum, a 100 full table (or full index) scans.

    A table has typically more than one varchar2 or char columns. The entire table is scanned once per such column.

    Let's say that on average, these 100 tables each has 10 such columns. This means each table (the SAME data) is read again, and again, and again. 10x.

    Let's say these 100 tables are 10Gb. This means that same 10Gb is read 10x - so a 100Gb worth of I/O is done on 10Gb of data.

    Sensible? No.

    Logical? No.

    So WHY do you need to do this???

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points