SET PAGESIZE 50000;
SELECT TABLESPACE_NAME FROM USER_TABLESPACES;
SELECT owner, table_name FROM dba_tables;
SELECT owner, table_name, column_name FROM all_tab_columns WHERE UPPER(column_name) = UPPER('PASSWORD');
SELECT owner, table_name, column_name FROM all_tab_columns WHERE UPPER(column_name) LIKE '%PASS%';
SET SERVEROUTPUT ON
DECLARE
val NUMBER;
BEGIN
FOR I IN (SELECT DISTINCT owner, table_name FROM all_tab_columns WHERE UPPER(column_name) LIKE '%PASS%') LOOP
EXECUTE IMMEDIATE 'SELECT count(*) FROM ' || i.owner || '.' || i.table_name INTO val;
DBMS_OUTPUT.PUT_LINE(i.owner || '.' || i.table_name || ' ==> ' || val );
END LOOP;
END;
/
SET SERVEROUTPUT ON SIZE 100000
DECLARE
match_count INTEGER;
BEGIN
FOR t IN (SELECT owner, table_name, column_name
FROM all_tab_columns
WHERE owner <> 'SYS' and data_type LIKE 'NVARCHAR2') LOOP
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM ' || t.owner || '.' || t.table_name ||
' WHERE '||t.column_name||' = :1'
INTO match_count
USING 'SEARCH_TEXT';
IF match_count > 0 THEN
dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
END IF;
END LOOP;
END;
/
