ORACLE 通过数据值查找找表名和字段名

有时候，我们只知道数据值，但需要通过这个数据值去查找数据是存放在哪个表的哪个字段。

DECLARE
V_SQL VARCHAR2(2000);
V_COUNT NUMBER;
BEGIN
FOR I IN (SELECT TABLE_NAME, COLUMN_NAME
FROM ALL_TAB_COLUMNS
WHERE OWNER = ‘USRMSI’) LOOP
BEGIN
V_SQL := ‘SELECT COUNT(1) FROM USRMSI.’ || I.TABLE_NAME || ’ WHERE ’ ||
I.COLUMN_NAME || ’ LIKE ‘’%172.17.0.92%‘’ ‘;
EXECUTE IMMEDIATE V_SQL
INTO V_COUNT;
IF (V_COUNT >= 1) THEN
DBMS_OUTPUT.PUT_LINE(‘SELECT USRMSI.’ || I.COLUMN_NAME || ’ FROM ’ ||
I.TABLE_NAME || ’ WHERE ’ || I.COLUMN_NAME ||
’ LIKE ‘’%172.17.0.92%’’ ');
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;