use all_tab_columns tables with add'l query 1

[cjmartin]

Hello,

I am attempting to do the following:

1) Select distinct table names from all_tab_columns where column_name = 'ABC' and owner = 'XYZ'

SQL:
SELECT distinct table_name
INTO tables_ABC_XYZ
FROM all_tab_columns
WHERE column_name = 'ABC'
AND owner = 'XYZ';

2) Query the tables identified with the first query based on a specific criteria.

SQL:
SELECT count...
FROM schema."table_ABC_XYZ"
WHERE 'ABC' = 123

3) Identify those tables that have at least one record returned based on the specific criteria

...and if count > 0, then list the table name (table_ABC_XYZ)

I believe a procedure (PL/SQL) can help me accomplish this, but I'm struggling with it.

Thanks for your help!!!

 

[sem]

declare
cnt integer;
begin
for f in (SELECT distinct table_name
FROM all_tab_columns
WHERE column_name = 'ABC'
AND owner = 'XYZ') loop
execute immediate
'select count(*) from '||f.table_name into cnt;
if cnt > 0 then
dbms_output.put_line(f.table_name||':'||cnt);
end if;
end loop;
end;

 

[cjmartin]

Thanks sem!!! That's EXACTLY what I was trying to accomplish...

 

[fmorel]

I developped procedures to search a string or a number in a schema. The procedure returns în which table and column the string/number was found abd how many times.
You may adapt them for your needs:

CREATE OR REPLACE PROCEDURE SEARCH_STRING (the_owner IN varchar2, the_string IN varchar2,
not_case_sensitive in integer default 0, like_operator in integer default 0 ) IS

/* ****************
* Search a string in a whole schema and displays in which tables and columns it is found.
* In SQL*PLUS, use serveroutput on
* This procedure should be created in SYS schema to have all the appropriate rights.
* SYNTAX:
* exec search_string ( OWNER, STRING [,CASE_SENSITIVE] [,LIKE_OPERATOR])
*
* OWNER: (string): the schema owner name
* STRING: the string to search. If LIKE_OPERATOR is used, then jockers (%, _) can be used
* CASE_SENSITIVE: numeric default value = 0 : YES -> case sensitive. Other values for NOT case sensitive
* LIKE_OPERATOR: numeric default value = 0 : NO -> operator '=' is used. Other values to use 'LIKE' operator
*
* Examples:
* set serveroutput on
* exec search_string('DWH_OWNER','USD');
* --> search all tables in schema 'DWH_OWNER' for the exact value 'USD'
* exec search_string('DWH_OWNER','%Lili%',1,1);
* --> search all tables in schema 'DWH_OWNER' for strings containing string 'LILI' (not case sensitive)
******************* */

TYPE CurTyp IS REF CURSOR;
cur_the_query CurTyp;
cur_columns CurTyp;
cursor c_tables is select table_name,owner from dba_tables where
owner=trim(upper(the_owner));
nb_occ NUMBER;
nb_col NUMBER;
v_colname varchar2(50);
v_query varchar2(32767);
v_col_query varchar2(32767);
v_operator varchar2(20) := ' = ';
v_function varchar2(20) := ' ';
v_string varchar2(255) ;
buf_size CONSTANT PLS_INTEGER := 500000;
begin
DBMS_OUTPUT.ENABLE (buf_size);
dbms_output.put_line('***** Searching string '''||the_string||''' in schema '''||the_owner||''' *****');

-- initialize variables
if like_operator!=0 then
v_operator:=' like ';
end if;
if not_case_sensitive!=0 then
v_string:=upper(the_string);
v_function:=' upper';
else
v_string:=the_string;
end if;

-- loop on tables
for v_table in c_tables loop
--dbms_output.put_line(v_table.table_name);
-- Search if data can be found
v_query := 'select count(*) as nb from '||v_table.owner||'.'||v_table.table_name||' where ';
v_col_query := 'select column_name from all_tab_columns c where data_type in (''VARCHAR2'', ''CHAR'') and table_name=:t and owner= and DATA_LENGTH>=:l';
nb_col:=0;
OPEN cur_columns FOR v_col_query USING v_table.table_name, v_table.owner, length(v_string);
LOOP
FETCH cur_columns into v_colname;
if cur_columns%FOUND and nb_col>0 then
v_query:=v_query||' or ';
end if;
if cur_columns%FOUND then
nb_col:=nb_col+1;
v_query:=v_query||v_function||'('||v_colname||')'||v_operator||''''||v_string||'''';
end if;
EXIT WHEN cur_columns%NOTFOUND;
END LOOP;
CLOSE cur_columns;
-- put_long_line(v_query);
if nb_col>0 then
OPEN cur_the_query FOR v_query;
FETCH cur_the_query INTO nb_occ;

-- if data found, search in which column
CLOSE cur_the_query;
if nb_occ>0 then
-- display number of occurences
dbms_output.put_line('.');
dbms_output.put_line('-----------------------');
dbms_output.put_line(v_table.table_name||' --> '||to_char(nb_occ)||' rows');
OPEN cur_columns FOR v_col_query USING v_table.table_name, v_table.owner, length(v_string);
LOOP
FETCH cur_columns into v_colname;
if cur_columns%FOUND then
v_query:='select count(*) as nb from '||v_table.owner||'.'||v_table.table_name||' where '||v_function||'('||v_colname||')'||v_operator||''''||v_string||'''';
OPEN cur_the_query FOR v_query;
FETCH cur_the_query INTO nb_occ;
if nb_occ>0 then
dbms_output.put_line('. '||v_colname||' -> '||to_char(nb_occ));
end if;
CLOSE cur_the_query;
end if;
EXIT WHEN cur_columns%NOTFOUND;
END LOOP;
CLOSE cur_columns;
end if;
end if;
END LOOP; --FOR
end;
/





CREATE OR REPLACE PROCEDURE SEARCH_NUMBER (the_owner IN varchar2, the_number IN FLOAT, number_of_decimals in number default NULL) IS

/* ****************
* Search a number in a whole schema and display in which tables and columns it is found.
* In SQL*PLUS, use serveroutput on
* This procedure should be created in SYS schema to have all the appropriate rights.
*
* SYNTAX:
* set serveroutput on
* exec search_number ( OWNER, NUMBER [,NUMBER_OF_DECIMALS])
*
* OWNER: (string): the schema owner name
* NUMBER: the number to search. If NUMBER_OF_DECIMALS is not null then search a rounded number
* NUMBER_OF_DECIMALS: numeric default value = NULL : ALL decimals, if not null then search rounded values using number of decimals
*
* Examples:
* set serveroutput on
* exec search_number('DWH_OWNER',1523.12);
* --> search all tables in schema 'DWH_OWNER' for the exact value 1523.12
* exec search_number('DWH_OWNER',1523.12,1);
* --> search all tables in schema 'DWH_OWNER' for numbers near 1523.1 (1523.05<= n < 1523.15)
* exec search_number('DWH_OWNER',1523.12,-2);
* --> search all tables in schema 'DWH_OWNER' for numbers near 1500 (1450<= n < 1550)
******************* */

TYPE CurTyp IS REF CURSOR;
cur_the_query CurTyp;
cur_columns CurTyp;
cursor c_tables is select table_name,owner from dba_tables where
owner=trim(upper(the_owner));
nb_occ NUMBER;
nb_col NUMBER;
v_colname varchar2(50);
v_query varchar2(32767);
v_col_query varchar2(32767);
v_function_begin varchar2(20) := ' ';
v_function_end varchar2(20) := ' ';
v_number FLOAT;
buf_size CONSTANT PLS_INTEGER := 500000;
begin
DBMS_OUTPUT.ENABLE (buf_size);


-- initialize variables
if number_of_decimals is not null then
v_function_begin:=' round( ';
v_function_end:=','||to_char(number_of_decimals)||' ) ';
v_number:=round(the_number,number_of_decimals);
else
v_number:=the_number;
end if;

dbms_output.put_line('***** Searching number '''||to_char(v_number)||''' with '||NVL(to_char(number_of_decimals),'ALL')||' decimals in schema '''||the_owner||''' *****');

-- loop on tables
for v_table in c_tables loop
--dbms_output.put_line(v_table.table_name);
-- Search if data can be found
v_query := 'select count(*) as nb from '||v_table.owner||'.'||v_table.table_name||' where ';
v_col_query := 'select column_name from all_tab_columns c where data_type in (''NUMBER'', ''FLOAT'') and table_name=:t and owner=';
nb_col:=0;
OPEN cur_columns FOR v_col_query USING v_table.table_name, v_table.owner;
LOOP
FETCH cur_columns into v_colname;
if cur_columns%FOUND and nb_col>0 then
v_query:=v_query||' or ';
end if;
if cur_columns%FOUND then
nb_col:=nb_col+1;
v_query:=v_query||v_function_begin||v_colname||v_function_end||'='||to_char(v_number);
end if;
EXIT WHEN cur_columns%NOTFOUND;
END LOOP;
CLOSE cur_columns;
--put_long_line(v_query);
if nb_col>0 then
OPEN cur_the_query FOR v_query;
FETCH cur_the_query INTO nb_occ;

-- if data found, search in which column
CLOSE cur_the_query;
if nb_occ>0 then
-- display number of occurences
dbms_output.put_line('.');
dbms_output.put_line('-----------------------');
dbms_output.put_line(v_table.table_name||' --> '||to_char(nb_occ)||' rows');
OPEN cur_columns FOR v_col_query USING v_table.table_name, v_table.owner;
LOOP
FETCH cur_columns into v_colname;
if cur_columns%FOUND then
v_query:='select count(*) as nb from '||v_table.owner||'.'||v_table.table_name||' where '||v_function_begin||v_colname||v_function_end||'='||to_char(v_number);
OPEN cur_the_query FOR v_query;
FETCH cur_the_query INTO nb_occ;
if nb_occ>0 then
dbms_output.put_line('. '||v_colname||' -> '||to_char(nb_occ));
end if;
CLOSE cur_the_query;
end if;
EXIT WHEN cur_columns%NOTFOUND;
END LOOP;
CLOSE cur_columns;
end if;
end if;
END LOOP; --FOR
end;
/