Re: How do you search through multiple tables?

On Wed, 19 Feb 2003, Norman.Dunbar_at_lfs.co.uk wrote:

> FOR x IN (SELECT table_name, column_name
> FROM user_tab_columns
> WHERE DATA_type IN ('VARCHAR2','CHAR')
> AND data_length >= LENGTH(TextToFind))
> LOOP
> BEGIN
> MyResult := 0;
> EXECUTE IMMEDIATE 'select 1 from dual where exists '||
> '(select 1 from '||x.table_name||
> ' where '||x.column_name||' like
> ''%'|| TextToFind||'%'')'
> INTO MyResult;
>
> -- If we got a hit, then list the table and column names.
> IF (MyResult = 1) THEN
> DBMS_OUTPUT.PUT_LINE(x.table_name || '.' ||
> x.column_name);
> END IF;
The thing I would add would be two loops. The outer loop is the list of tables. The inner loop finds all the appropriate columns to query and concatenates them together so the query would look sort of like,

    SQL> select * from t3;

    FLD1 FLD2

• ---------- AA BB BB CC AA CC DA CB

    SQL> select * from t3 where fld1 || fld2 like '%A%C%';

    FLD1 FLD2

• ---------- AA CC DA CB

Then, you only scan each table once.

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.