This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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; |
IT from Hell 
Leave a Reply