7 Replies Latest reply: Feb 8, 2011 9:35 AM by Hoek RSS

    How to find the column name and table name with a value

    Srikkanth.M
      Hi All


      How to find the column name and table name with "Value".

      For Example i have value named "Srikkanth" This value will be stored in one table and in one column i we dont know the table how to find the table name and column name


      Any help is highly appricatable


      Thanks & Regards
      Srikkanth.M
        • 1. Re: How to find the column name and table name with a value
          user12869307
          Select * from user_tab_cols; will give table_name,column_name and few other details from that particular schema... Most probably the name column will contain "NAME" word in column name ... so we can get those tables and try searching...... I answered in the way I know... I am sorry if this is not your requirement..
          Thanks...
          • 2. Re: How to find the column name and table name with a value
            Dbb
            dba_ all_ user_
            set serveroutput on 
            Begin
             For Cur in ( Select  'select 1 from ' ||  owner || '.' || table_name || ' where ' || column_name  || ' = ''Srikkanth'''  sentence from all_tab_columns) loop
               begin
               execute immediate (Cur.sentence);
               dbms_output.put_line (Cur.sentence);
               exception when no_data_found then null;
               end;
            
             end loop;
            End;
            Then sit down and wait.

            ''Srikkanth''' sorry

            Edited by: Dbb on 13-ene-2011 14:41

            from all_tab_columns where data_type = 'VARCHAR2'

            Edited by: Dbb on 13-ene-2011 14:46

            also you can use a when others then null;

            Edited by: Dbb on 13-ene-2011 14:47
            • 3. Re: How to find the column name and table name with a value
              BluShadow
              2 solutions by Michaels (the latter is 11g upwards only)...
              michaels>  var val varchar2(5)
               
              michaels>  exec :val := 'as'
              PL/SQL procedure successfully completed.
               
              michaels>  select distinct substr (:val, 1, 11) "Searchword",
                              substr (table_name, 1, 14) "Table",
                              substr (t.column_value.getstringval (), 1, 50) "Column/Value"
                         from cols,
                              table
                                 (xmlsequence
                                     (dbms_xmlgen.getxmltype ('select ' || column_name
                                                              || ' from ' || table_name
                                                              || ' where upper('
                                                              || column_name
                                                              || ') like upper(''%' || :val
                                                              || '%'')'
                                                             ).extract ('ROWSET/ROW/*')
                                     )
                                 ) t
              --        where table_name in ('EMPLOYEES', 'JOB_HISTORY', 'DEPARTMENTS')
                     order by "Table"
              or
              SQL> select table_name, 
                     column_name, 
                     :search_string search_string, 
                     result
                from cols, 
                     xmltable(('ora:view("'||table_name||'")/ROW/'||column_name||'[ora:contains(text(),"%'|| :search_string || '%") > 0]')
                     columns result varchar2(10) path '.'
                     )
              where table_name in ('EMP', 'DEPT')
              / 
              TABLE_NAME           COLUMN_NAME          SEARCH_STRING        RESULT    
              -------------------- -------------------- -------------------- ----------
              DEPT                 DNAME                ES                   RESEARCH  
              DEPT                 DNAME                ES                   SALES     
              EMP                  ENAME                ES                   JONES     
              EMP                  ENAME                ES                   JAMES     
              EMP                  JOB                  ES                   SALESMAN  
              EMP                  JOB                  ES                   SALESMAN  
              EMP                  JOB                  ES                   SALESMAN  
              EMP                  JOB                  ES                   PRESIDENT 
              EMP                  JOB                  ES                   SALESMAN  
               
              9 rows selected.
              • 4. Re: How to find the column name and table name with a value
                Srikkanth.M
                Hi

                thanks For ur suggestion.

                i am working in production environment and i give the name for example, but my searching for a Error code like W10058 but i dont know the table and column name.


                If it possible to find by using TEXT??

                Thanks & Regards
                Srikkanth.M
                • 5. Re: How to find the column name and table name with a value
                  MichaelS
                  (the latter is 11g upwards only)...
                  They changed it again :(
                  SQL> select * from v$version where rownum = 1
                  /
                  BANNER                                                                          
                  --------------------------------------------------------------------------------
                  Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production          
                  1 row selected.
                  
                  
                  SQL> var search_string varchar2(10)
                  
                  SQL> exec :search_string := 'es'
                  PL/SQL procedure successfully completed.
                  
                  SQL> select table_name, 
                         column_name, 
                         :search_string search_string, 
                         result
                    from cols, 
                         xmltable(('ora:view("'||table_name||'")/ROW/'||column_name||'[ora:contains(text(),"%'|| :search_string || '%") > 0]')
                         columns result varchar2(10) path '.'
                         )
                  where table_name in ('EMP', 'DEPT')
                  /
                                               *
                  Error at line 6
                  ORA-02000: missing COLUMNS keyword
                  following works though:
                  SQL> select table_name,
                         column_name,
                         :search_string search_string,
                         result
                    from (select column_name,
                                 table_name,
                                 'ora:view("' || table_name || '")/ROW/' || column_name || '[ora:contains(text(),"%' || :search_string || '%") > 0]' str
                            from cols
                           where table_name in ('EMP', 'DEPT')),
                         xmltable (str columns result varchar2(10) path '.')
                  /
                  TABLE_NAME                     COLUMN_NAME                    SEARCH_STRING                    RESULT    
                  ------------------------------ ------------------------------ -------------------------------- ----------
                  DEPT                           DNAME                          es                               RESEARCH  
                  EMP                            ENAME                          es                               JAMES     
                  EMP                            JOB                            es                               SALESMAN  
                  EMP                            JOB                            es                               SALESMAN  
                  
                  4 rows selected.
                  • 6. Re: How to find the column name and table name with a value
                    BluShadow
                    Thanks Michael, my standard examples library is now updated.
                    • 7. Re: How to find the column name and table name with a value
                      Hoek
                      I was using this option, since we're (still) not using 11g over here, and I got:
                      SQL> select * from v$version where rownum=1;
                      
                      BANNER
                      ----------------------------------------------------------------
                      Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
                      
                      1 row selected.
                      
                      SQL> with c as ( select table_name
                        2              ,      column_name
                        3              from   cols
                        4              where  data_type = 'VARCHAR2'            
                        5            )
                        6  select distinct 'jong' "Searchword",
                        7                  substr (table_name, 1, 14) "Table",
                        8                  substr (t.column_value.getstringval (), 1, 50) "Column/Value"
                        9  from   c
                       10  ,      table(xmlsequence(dbms_xmlgen.getxmltype( ' select ' || column_name
                       11                                                 ||' from ' || table_name
                       12                                                 ||' where upper('
                       13                                                 ||column_name
                       14                                                 || ') like upper(''%jong%'')'
                       15                                                 ).extract('ROWSET/ROW/*')
                       16                          )
                       17              ) t
                       18  order by "Table";
                      ,      table(xmlsequence(dbms_xmlgen.getxmltype( ' select ' || column_name
                                               *
                      ERROR at line 10:
                      ORA-19202: Error occurred in XML processing
                      ORA-00932: inconsistent datatypes: expected NUMBER got BLOB
                      ORA-06512: at "SYS.DBMS_XMLGEN", line 288
                      ORA-06512: at line 1
                      No matter what I tried sofar, even restricting datatype on VARCHAR2 doesn't make the query work.
                      However, when I restrict on certain tablenames the query runs fine.

                      Does anyone have an idea what might be the cause of the error?
                      Currently I'm out of ideas regarding what else I need to restrict on...

                      edit
                      Adding a restriction on NUM_NULLS > 0 appears to work

                      Edited by: hoek on Feb 8, 2011 4:33 PM