| 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