Outputs DML for selecting from tables which have matching data in a specific column name. In the example below a search is done on all tables
with column name COUNTRY_NAME which contains data like 'United%'. Running the first DML statement in this case returned records for:
United Arab Emirates
United Kingdom
United States
United States Minor Outlying Islands
SQL> @findit
Purpose : Finds records containing values for a given column name
Column name: country_name
If using LIKE include the % before and after where relevant
Value: United%
Ignore owners=/APEX_030200/APEX_PUBLIC_USER/DBSNMP/EXFSYS/FLOWS_FILES/MDSYS/ORACLE_OCM/ORDDATA/ORDPLUGINS/ORDSYS/OUTLN/OWBSYS/OWBSYS_AUDIT/SYS/SYSMAN/SYSTEM/WMSYS/XDB/
Ignore owners [as above default] :
Include owners like [null] : refere
Tables like :
Operand [=] : like
Show zero counts [N] :
Failed
------
Data Found
----------
REFERENCE_DEV COUNTRIES 4 records found - select 'REFERENCE_DEV' "Owner", a1. * from REFERENCE_DEV.COUNTRIES a1 where COUNTRY_NAME like 'United%';
REFERENCE_DEV COUNTRIES_NPK 4 records found - select 'REFERENCE_DEV' "Owner", a1. * from REFERENCE_DEV.COUNTRIES_NPK a1 where COUNTRY_NAME like 'United%';
REFERENCE_DEV DEMO_COUNTRIES 4 records found - select 'REFERENCE_DEV' "Owner", a1. * from REFERENCE_DEV.DEMO_COUNTRIES a1 where COUNTRY_NAME like 'United%';
REFERENCE_UAT COUNTRIES 4 records found - select 'DATAM_A_B_2' "Owner", a1. * from DATAM_A_B_2.COUNTRIES a1 where COUNTRY_NAME like 'United%';
REFERENCE_UAT COUNTRIES_NPK 2 records found - select 'DATAM_A_B_2' "Owner", a1. * from DATAM_A_B_2.COUNTRIES_NPK a1 where COUNTRY_NAME like 'United%';
REFERENCE_MID COUNTRIES 2 records found - select 'REFERENCE_MID' "Owner", a1. * from REFERENCE_MID.COUNTRIES a1 where COUNTRY_NAME like 'United%';
REFERENCE_MID COUNTRIES_NPK 2 records found - select 'REFERENCE_MID' "Owner", a1. * from REFERENCE_MID.COUNTRIES_NPK a1 where COUNTRY_NAME like 'United%';
REFERENCE_SYS COUNTRIES 2 records found - select 'REFERENCE_SYS' "Owner", a1. * from REFERENCE_SYS.COUNTRIES a1 where COUNTRY_NAME like 'United%';
REFERENCE_SYS COUNTRIES_NPK 2 records found - select 'REFERENCE_SYS' "Owner", a1. * from REFERENCE_SYS.COUNTRIES_NPK a1 where COUNTRY_NAME like 'United%';
PL/SQL procedure successfully completed.
-- Name : sga_string.sql
prompt
prompt Purpose : Finds records containing values for a given column name
prompt
-- Date last modified : 2015/09/29
-- Oracle Version: 11g
-- Other Oracle versions: should work on all post-9i versions
----------------------------------------------------------------------------------------
prompt
accept x_column prompt "Column name: "
prompt
prompt If using LIKE include the % before and after where relevant
prompt
accept x_value prompt "Value: "
prompt
prompt Ignore owners=/APEX_030200/APEX_PUBLIC_USER/DBSNMP/EXFSYS/FLOWS_FILES/MDSYS/ORACLE_OCM/ORDDATA/ORDPLUGINS/ORDSYS/OUTLN/OWBSYS/OWBSYS_AUDIT/SYS/SYSMAN/SYSTEM/WMSYS/XDB/
prompt
accept x_ignore prompt "Ignore owners [as above default] : " default '/APEX_030200/APEX_PUBLIC_USER/DBSNMP/EXFSYS/FLOWS_FILES/MDSYS/ORACLE_OCM/ORDDATA/ORDPLUGINS/ORDSYS/OUTLN/OWBSYS/OWBSYS_AUDIT/SYS/SYSMAN/SYSTEM/WMSYS/XDB/'
prompt
accept x_include_like prompt "Include owners like [null] : "
prompt
accept x_include_table prompt "Tables like : "
prompt
accept x_operand prompt "Operand [=] : " default '='
prompt
accept x_show_zero prompt "Show zero counts [N] : " default 'N'
prompt
prompt
declare
ls_sqlerrm varchar2( 1000 );
ls_column varchar2( 100 ) := upper( '&&x_column' );
ls_value varchar2( 100 ) := '&&x_value';
ls_sql varchar2( 4000 );
ls_sql1 varchar2( 4000 );
ln_counter number;
cursor c_tables is
select unique owner, table_name, data_type
from dba_tab_columns
where instr( upper( '&&x_ignore' ), '/' || owner || '/' ) = 0
and ( '&&x_include_like' is null or owner like upper( '%&&x_include_like%' ) )
and ( '&&x_include_table' is null or table_name like upper( '%&&x_include_table%' ) )
and column_name = ls_column order by 1,2;
ln_failed number := 0;
ln_records number := 0;
ln_empty number := 0;
type tt_outlines is table of varchar2( 1000 ) index by binary_integer;
ltt_failed tt_outlines;
ltt_records tt_outlines;
ltt_empty tt_outlines;
begin
for i in c_tables loop
begin
if i.data_type in ( 'DATE', 'FLOAT', 'NUMBER' )
or substr( i.data_type, 1, 9 ) = 'TIMESTAMP'
or substr( i.data_type, 1, 8 ) = 'INTERVAL' then
ls_sql := 'select count(*) from ' || i.owner || '.' || i.table_name || ' where ' || ls_column || ' ' || '&&x_operand' || ' ' || ls_value;
ls_sql1 := 'select ' || '''' || i.owner || '''' || ' "Owner", a1.* from ' || i.owner || '.' || i.table_name || ' a1 where ' || ls_column || ' ' || '&&x_operand' || ' ' || ls_value;
else
ls_sql := 'select count(*) from ' || i.owner || '.' || i.table_name || ' where ' || ls_column || ' ' || '&&x_operand' || ' ' || '''' || ls_value || '''';
ls_sql1 := 'select ' || '''' || i.owner || '''' || ' "Owner", a1. * from ' || i.owner || '.' || i.table_name || ' a1 where ' || ls_column || ' ' || '&&x_operand' || ' ' || '''' || ls_value || '''' || ';';
end if;
execute immediate ls_sql into ln_counter;
if ln_counter = 0 then
ln_empty := ln_empty + 1;
ltt_empty( ln_empty ) := rpad( i.owner, 30 ) || rpad( i.table_name, 30 ) || ' -';
else
ln_records := ln_records + 1;
ltt_records( ln_records ) := rpad( i.owner, 30 ) || rpad( i.table_name, 30 ) || ' ' || to_char( ln_counter, '9999' ) || ' records found - ' || ls_sql1 || chr(10);
-- ln_records := ln_records + 1;
-- ltt_records( ln_records ) := rpad( ' ', 30 ) || rpad( ' ', 30 ) || ' - ' || ls_sql1 || chr(10);
end if;
exception
when others then
ls_sqlerrm := sqlerrm;
ln_failed := ln_failed + 1;
ltt_failed( ln_failed ) := rpad( i.owner, 30 ) || rpad( i.table_name, 30 ) || ' - [DATATYPE:' || i.data_type || '] ' || ls_sql || ' ' || ls_sqlerrm;
end;
end loop;
ln_counter := 0;
dbms_output.put_line( chr(10) );
dbms_output.put_line( 'Failed' );
dbms_output.put_line( '------' );
for i in 1..ln_failed loop
dbms_output.put_line( ltt_failed ( i ) );
end loop;
if upper( substr( '&&x_show_zero prompt', 1, 1 ) ) = 'Y' then
ln_counter := ln_counter + 1;
dbms_output.put_line( chr(10) );
dbms_output.put_line( 'Empty' );
dbms_output.put_line( '-----' );
for i in 1..ln_empty loop
dbms_output.put_line( ltt_empty ( i ) );
end loop;
end if;
dbms_output.put_line( chr(10) );
dbms_output.put_line( 'Data Found' );
dbms_output.put_line( '----------' );
for i in 1..ln_records loop
dbms_output.put_line( ltt_records ( i ) );
ln_counter := ln_counter + 1;
end loop;
if ln_counter = 0 then
dbms_output.put_line( chr(10) );
dbms_output.put_line( 'Empty' );
dbms_output.put_line( '-----' );
for i in 1..ln_empty loop
dbms_output.put_line( ltt_empty ( i ) );
end loop;
end if;
end;
/