DECLARE
match_count integer;
v_search_string varchar2(4000) :='%MYSTR%';
BEGIN
FOR t IN (SELECT owner, table_name, column_name FROM all_tab_columns WHERE DATA_TYPE ='CHAR' ) LOOP
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM '||t.owner || '.' || t.table_name||
' WHERE '||t.column_name||' like :1'
INTO match_count
USING v_search_string;
IF match_count > 0 THEN
dbms_output.put_line( t.owner || '.' || t.table_name ||' '||t.column_name||' '||match_count );
END IF;
END LOOP;
END;
我這只查出 DATA_TYPE='CHAR' 請依需求改成 VARCHAR2 NUMBER....等等.
