11 Replies Latest reply: Jan 15, 2013 3:34 AM by BluShadow RSS

    SQL  to find which table data is stored?

    589451
      I know a table field value.

      I don't know, which table that value is stored. I want to find that table from database.

      What sql will find that?.

      ---------------------------------------------
      Please post me online links for tricky sql's like this...so i can master sql's of this kind.


      Thanks for ur help.
        • 1. Re: SQL  to find which table data is stored?
          518838
          There are no such tricks to find such type of data only by knowing the values.

          If you know the column name, then you can find through all_tab_columns. But then also you'll have to create a script using below query and run the result.

          select 'select * from '|| table_name || ' where '|| column_name || ' like ''%''' from all_tab_columns where column_name like '%ABC%'

          Deepak
          • 2. Re: SQL  to find which table data is stored?
            BluShadow
            ok, you're going to have to look at user_tab_columns e.g.

            SQL> desc user_tab_columns

            and this will detail a data dictionary view that details the tables and their columns

            From this, you're going to have to build up an SQL select statement as a string that queries each table in turn checking each of the relevant columns, i.e. if you are searching for a particular date then you only need to deal with the DATE columns of each table.

            To execute the SQL statement you will need to use EXECUTE IMMEDIATE as you are dealing with dynamic SQL; which, incidentally, you are going to have to write in PL/SQL.

            When you've had a go, if you're stuck, post it on here and we can guide you as to where you are going wrong.
            • 3. Re: SQL  to find which table data is stored?
              589451
              This is possible yaar...

              my dba did this for me....oh!...i forgot to store that sql....

              there is some tricky sql can find which table that value is stored.

              i know only value...i got more than 500 tables in database....

              i don't which table that data is, which column that data is stored.

              how can i find table and column name that data is stored?.
              • 4. Re: SQL  to find which table data is stored?
                388131
                how can i find table and column name that data is stored?.
                Haven't you read what BluShadow has written? And I doubt that there's a better way than the one he described.
                • 5. Re: SQL  to find which table data is stored?
                  518838
                  Hey dear probably you don't mean what do you want.
                  You're trying to find a person living in the world or a country or a city.....
                  Do you know the way to find a particular person in a country only by his name??

                  Anyways if you're concerned only with the data not the performance, then you can user below thing

                  select * from all_tab_columns where owner like '%%' and table_name like '%%' and owner='<DATABASE_USER>'

                  and then use the result set in a pl/sql procedure. This will definetely give you the desired result, but how much time this will take, can't be predicted.

                  All the best
                  Deepak
                  • 6. Re: SQL  to find which table data is stored?
                    MichaelS
                    Ok, here's my pseudo google DB:
                    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"
                    
                    Searchword  Table          Column/Value                                      
                    ----------- -------------- --------------------------------------------------
                    as          DEPARTMENTS    <DEPARTMENT_NAME>Purchasing</DEPARTMENT_NAME>     
                    as          DEPARTMENTS    <DEPARTMENT_NAME>Treasury</DEPARTMENT_NAME>       
                    as          EMPLOYEES      <EMAIL>PVARGAS</EMAIL>                            
                    as          EMPLOYEES      <EMAIL>STOBIAS</EMAIL>                            
                    as          EMPLOYEES      <FIRST_NAME>Douglas</FIRST_NAME>                  
                    as          EMPLOYEES      <FIRST_NAME>Jason</FIRST_NAME>                    
                    as          EMPLOYEES      <JOB_ID>AD_ASST</JOB_ID>                          
                    as          EMPLOYEES      <LAST_NAME>Tobias</LAST_NAME>                     
                    as          EMPLOYEES      <LAST_NAME>Vargas</LAST_NAME>                     
                    as          JOB_HISTORY    <JOB_ID>AD_ASST</JOB_ID>                          
                    
                    
                    10 rows selected.
                    Not as fast as google, so you better take a cup of tea or two ... ;-)
                    • 7. Re: SQL  to find which table data is stored?
                      589451
                      thanks michaels.

                      u r great.

                      do u know any online links where i can learn this type of complicated SQL's.

                      Thanks
                      • 8. Re: SQL  to find which table data is stored?
                        John Spencer
                        Michael:

                        I posted Re: How to find out a tablename to the same problem using xxx_tab_columns and execute immediate some time ago. I'm not sure that it would be any faster than yours, but it does limit the number of columns and, at least potentially the number of tables that needs to be searched. Mine also works if you don't even know the owner of the table.

                        However, if I try to use a similar structure with your XML version I get errors. As the hr user, I tried this variant of yours:
                        SELECT owner, table_name, substr (:val, 1, 11) "Searchword",
                               SUBSTR(t.column_value.getstringval (),1, 50) "Column/Value"
                        from all_tab_columns,
                             table(xmlsequence(dbms_xmlgen.getxmltype ('select ' || column_name
                                                                        || ' from ' ||owner||'.'||table_name
                                                                        || ' where upper('
                                                                        || column_name
                                                                        || ') like upper(''%' || :val
                                                                        || '%'')'
                                                                       ).extract ('ROWSET/ROW/*')
                                               )
                                           ) t
                        where owner NOT IN ('SYS','SYSTEM') and
                              data_type IN ('CHAR','VARCHAR2') and
                              data_length >= LENGTH(:val) and
                              table_name in (SELECT table_name from all_tables)
                        Which errors with
                        ORA-19206: Invalid value for query or REF CURSOR parameter
                        ORA-06512: at "SYS.DBMS_XMLGEN", line 206
                        ORA-06512: at line 1
                        Oddly enough, if I replace the last predicate with table_name = '&t' and then run it with each of the table names that come from the query:
                        SELECT table_name FROM all_objects
                        where owner NOT IN ('SYS','SYSTEM') and
                              data_type IN ('CHAR','VARCHAR2') and
                              data_length >= LENGTH(:val) and
                              table_name in (SELECT table_name from all_tables)
                        They all run successfully. Is there some sort of limit I'm hitting here or have I just fundamentally mis-understood how this is supposed to work? The latter seems quite likely to me since I do almost no work with XML.

                        Thanks
                        John
                        • 9. Re: SQL  to find which table data is stored?
                          682120
                          OK,Tq sir ,I am not going to use this in Production side .
                          • 10. Re: SQL  to find which table data is stored?
                            Nitesh.
                            Hey try to ask that DBA once again n let me know the solution for this also as i even need the solution for same case .. Coz I am having Oracle ADF as Frontend and So after modifying in frontend, I have to check in Back End Database , So through values i can check easily so i too require this option .. So try to let me know too if u get to knw about it ...
                            • 11. Re: SQL  to find which table data is stored?
                              BluShadow
                              Thanks for dragging up a 5 and a half year old thread.

                              If you have an issue of your own, please start your own thread with your question, giving as much details as possible.

                              {message:id=9360002}

                              Locking this thread