oracle 如何搜索当前用户下所有表里含某个值的字段？(转)

可以使用以下 SQL 查询语句：

SELECT TABLE_NAME, COLUMN_NAME
FROM USER_TAB_COLUMNS
WHERE UPPER(DATA_TYPE) LIKE '%CHAR%'
AND UPPER(TABLE_NAME) NOT LIKE 'BIN$%'
AND UPPER(TABLE_NAME) NOT LIKE '%_AUD%'
AND UPPER(COLUMN_NAME) NOT LIKE '%SYS_%'
AND UPPER(COLUMN_NAME) NOT LIKE '%ROWID%'
AND UPPER(COLUMN_NAME) NOT LIKE '%VERSION%'
AND UPPER(COLUMN_NAME) NOT LIKE '%PTIME%'
AND UPPER(COLUMN_NAME) NOT LIKE '%M_ROW$$%'
AND UPPER(COLUMN_NAME) NOT LIKE '%MDEXTEND%'
AND UPPER(COLUMN_NAME) NOT LIKE '%MDXROWSET%'
AND UPPER(COLUMN_NAME) NOT LIKE '%MDXRULES%'
AND (
    UPPER(TABLE_NAME) LIKE '%<SEARCH_TERM>%'
    OR UPPER(COLUMN_NAME) LIKE '%<SEARCH_TERM>%'
)
ORDER BY TABLE_NAME ASC, COLUMN_NAME ASC;

其中 `<SEARCH_TERM>` 是你要搜索的值。这个查询语句会在当前用户下的所有表中搜索所有定义为字符型的字段，并返回包含 `<SEARCH_TERM>` 的表名和字段名。同时，它也会排除一些系统表和不需要搜索的字段。