set serveroutput on size 100000; | |
declare | |
–Text to find, case insensitive. Use % for like. | |
V_TEXT varchar(50) := '%what I want to locate%'; | |
–Tables schema | |
V_SCHEMA varchar(50) := 'SCHEMA_NAME'; | |
–List of tables to skip; comma-separated or NONE | |
V_SKIP_NAMES varchar(200) := 'NONE'; | |
–Using temporary table | |
V_TEMPORARY_TABLE number := 0; | |
—————————– | |
V_TOTAL_TABLES number := 0; | |
V_TOTAL_COLUMNS number := 0; | |
V_TOTAL_RESULT number := 0; | |
V_TIMER number; | |
V_QUERY varchar(4000); | |
–Select all schema's tables | |
cursor | |
V_TABLE_LIST | |
is | |
select | |
OBJECT_NAME | |
from | |
ALL_OBJECTS | |
where | |
(OBJECT_TYPE = 'TABLE') | |
and (OWNER = V_SCHEMA) | |
–Eventual exclusions | |
and (OBJECT_NAME not in (V_SKIP_NAMES)) | |
–Debug purposes | |
–and (OBJECT_NAME in ('GM_SFL_SERVICE_FEE_DETAILS')) | |
order by | |
OBJECT_NAME | |
; | |
begin | |
V_TIMER := dbms_utility.get_time(); | |
begin | |
if (V_TEMPORARY_TABLE = 1) then | |
execute immediate 'create global temporary table TMP_LOOKUP_TEXT (SCHEMA_NAME varchar(50), TABLE_NAME varchar2(50), COLUMN_NAME varchar2(50), COLUMN_VALUE varchar2(1000)) on commit preserve rows'; | |
end if; | |
exception when others then | |
dbms_output.put_line('Fail to create temporary table: ' || sqlerrm); | |
end; | |
–For each table | |
for V_TABLE_ITEM in V_TABLE_LIST loop | |
V_TOTAL_TABLES := V_TOTAL_TABLES + 1; | |
–dbms_output.put_line('Checking table: ' || V_TABLE_ITEM.OBJECT_NAME); | |
declare | |
–Select all table's columns | |
cursor | |
V_COLUMN_LIST | |
is | |
select | |
OWNER as SCHEMA_NAME | |
, TABLE_NAME | |
, COLUMN_NAME | |
, DATA_TYPE | |
from | |
SYS.ALL_TAB_COLUMNS | |
where | |
(OWNER = V_SCHEMA) | |
and (TABLE_NAME = V_TABLE_ITEM.OBJECT_NAME) | |
and (COLUMN_NAME not in (V_SKIP_NAMES)) | |
and (DATA_TYPE in ('NVARCHAR2', 'CHAR', 'VARCHAR2')) | |
order by | |
COLUMN_NAME | |
; | |
begin | |
–For each column | |
for V_COLUMN_ITEM in V_COLUMN_LIST loop | |
V_TOTAL_COLUMNS := V_TOTAL_COLUMNS + 1; | |
–dbms_output.put_line('Checking column: ' || V_COLUMN_ITEM.COLUMN_NAME); | |
V_QUERY := 'select ' || V_COLUMN_ITEM.COLUMN_NAME || ' from ' || V_COLUMN_ITEM.SCHEMA_NAME || '.' || V_TABLE_ITEM.OBJECT_NAME || ' where upper(' || V_COLUMN_ITEM.COLUMN_NAME || ') like upper(' || chr(39) || V_TEXT || chr(39) || ')'; | |
–dbms_output.put_line('Query: ' || V_QUERY); | |
declare | |
type V_MATCH_RECORD is record | |
( | |
V_VALUE varchar2(10000) | |
); | |
type V_MATCH_TABLE is table of V_MATCH_RECORD; | |
type V_MATCH_CURSOR_TYPE is ref cursor; | |
V_MATCH_LIST V_MATCH_TABLE; | |
V_MATCH_SELECT V_MATCH_CURSOR_TYPE; | |
V_INDEX binary_integer; | |
begin | |
–Select columns where the value matches | |
open V_MATCH_SELECT for V_QUERY; | |
fetch V_MATCH_SELECT bulk collect into V_MATCH_LIST; | |
close V_MATCH_SELECT; | |
–Found? | |
if (V_MATCH_LIST.count > 0) then | |
V_INDEX := V_MATCH_LIST.first; | |
while V_INDEX is not null loop | |
V_TOTAL_RESULT := V_TOTAL_RESULT + 1; | |
if (V_TEMPORARY_TABLE = 0) then | |
dbms_output.put_line(V_COLUMN_ITEM.SCHEMA_NAME || '.' || V_COLUMN_ITEM.TABLE_NAME || '.' || V_COLUMN_ITEM.COLUMN_NAME || ' = ' || substr(V_MATCH_LIST(V_INDEX).V_VALUE, 1, 50)); | |
else | |
execute immediate 'insert into TMP_LOOKUP_TEXT (SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, COLUMN_VALUE) values (V_COLUMN_ITEM.SCHEMA_NAME, V_COLUMN_ITEM.TABLE_NAME, V_COLUMN_ITEM.COLUMN_NAME, V_MATCH_LIST(V_INDEX).V_VALUE)'; | |
end if; | |
V_INDEX := V_MATCH_LIST.next(V_INDEX); | |
end loop; | |
end if; | |
exception when others then | |
dbms_output.put_line('Fail to report match: ' || sqlerrm); | |
end; | |
end loop; | |
end; | |
end loop; | |
V_TIMER := dbms_utility.get_time() – V_TIMER; | |
dbms_output.put_line('Read ' || V_TOTAL_COLUMNS || ' columns from ' || V_TOTAL_TABLES || ' tables in ' || (V_TIMER / 1000) || ' seconds. Found ' || V_TOTAL_RESULT || ' match to ' || chr(39) || V_TEXT || chr(39) || ' in schema ' || V_SCHEMA || '.'); | |
begin | |
if (V_TEMPORARY_TABLE = 1) then | |
execute immediate 'select * from TMP_LOOKUP_TEXT order by TABLE_NAME, COLUMN_NAME'; | |
end if; | |
exception when others then | |
dbms_output.put_line('Fail to list result in temporary table: ' || sqlerrm); | |
end; | |
end; |
09/Apr/2021
Leave a Reply