4 Replies Latest reply: Jan 21, 2013 1:35 AM by Billy~Verreynne RSS

    findout table name and column name

    874273
      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
          HOW To Make TUNING request
          SQL and PL/SQL FAQ
          • 2. Re: findout table name and column name
            Billy~Verreynne
            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
              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
                Billy~Verreynne
                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???