Basic SQL Question 1

[SmpTraining]

Hi guys,

I'm having trouble finding a table/field name so I thought of entering a rogue value using the front-end and searching all the fields for this value.

I don't know how to write the SQL for this though.

select distinct t.table_name, f.field_name 
from user_tables t, dict_flds f
where t.table_name = f.table_name

lists all the fields but how do I write a statement to check for the specific value?

Thanks for any help.


Steve Phillips, Crystal Trainer/Consultant

http://www.smptraining.co.uk

 

[KenCunningham]

Can't you use dba_tab_columns for this:

select table_name, column_name
from dba_tab_columns
where column_name = 'whatever'

Or have I misunderstood the requirement?

 

[SmpTraining]

Your SQL shows a neater way to query the data definition but that still doesn't solve the problem.

I do not know what the column name is that I'm searching for so I'm entering a rogue value into the front-end and I'm trying to search all the records (for ALL columns in ALL tables!!) to locate the column name(s) containing the rogue value.

There may be a very simple way to do this as I'm not used to using Oracle in this way.

Steve Phillips, Crystal Trainer/Consultant

http://www.smptraining.co.uk

 

[KenCunningham]

OK, Steve, take out the where clause and replace with:

order by table_name, column_name

to get a listing for all table/column combinations in the database. You might like to include a 'set pause on' at the beginning so that you have to press return to continue the listing after examining a 'page full'. Alternatively, write the results to a file using 'spool <filename>' at the beginning, to be examined/searched at your leisure. You can also limit the tables to be searched by including a 'where owner = <owner of the table>' clause too, since you might not be interested in (for example) system-owned tables.

 

[SmpTraining]

Thanks for the responses Ken but I've obviously not explained myself very well. If I follow your instructions I will have a list of tables and columns.

That is not what I'm after. What I want to do is effectively run a select statement against every table/column combination looking for a rogue value.

For example: At the front-end I enter a value of 1234.56 into a numeric field. I now need to know what table and column name is holding that information in the database.

The database documentation is poor in this area and I thought I should be able to scan the oracle tables using some SQL command and it's here that I need some help.

Thanks again for your prompt responses. Please let me know if I need to explain in more detail.

Steve Phillips, Crystal Trainer/Consultant

http://www.smptraining.co.uk

 

[KenCunningham]

Sorry, Steve. You're quite correct, I did entirely miss the point! Mandayitis probably.

Let me have a think about this and get back to you if I come up with anything. If not, I'm sure someone with experience of this sort of thing will be along soon.

 

[SantaMufasa]

SM,

Actually, your need is far from a "Basic SQL Question". I'm tied up right now else I'd try to build a solution for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[carp]

Well, it's a PL/SQL-based solution, but the following will do what you are asking for:

set serveroutput on size 1000000
DECLARE
   l_count NUMBER;
BEGIN
   FOR i IN (SELECT owner, table_name, column_name 
               FROM all_tab_columns
              WHERE data_type = 'NUMBER') LOOP
      EXECUTE IMMEDIATE 'SELECT count(*) FROM '||i.owner||'.'||i.table_name||
                         ' WHERE '||i.column_name||' = 1234.56 AND rownum = 1' INTO l_count;
      IF (l_count > 0) THEN 
         dbms_output.put_line(i.owner||'.'||i.table_name||'.'||i.column_name);
      END IF;
   END LOOP;
END;
/

Be advised that (depending on how many tables you have access to), this may take approximately forever to run.
If this is something you plan on doing more than once, you would probably want to parameterize the target value and data type.

 

[SmpTraining]

Just what I was looking for carp. Thank you.


Steve Phillips, Crystal Trainer/Consultant

http://www.smptraining.co.uk