查询以在数据库的所有表中查找值 [英] query to find the value in all the tables of the database

问题描述

我正在搜索表中的特定值，并且有很多表，是否存在查询以在数据库的所有表中找到该值，因此我可以快速找到该值而无需逐一遍历每个表一个.

I am searching for a specific value in the table and there are lot of tables, is there a query to find the value in all the tables of the database so that I can quickly find the value without going through each table one by one.

我已经尝试过

SELECT owner, table_name, column_name FROM all_tab_columns WHERE column_name LIKE '%52871%';

SELECT * from dba_objects WHERE object_name like '%52871%'

推荐答案

您可以搜索使用XML SQL在整个SCHEMA中所有表的所有列中的值:

例如，我要在整个SCOTT架构的所有表的所有列中搜索值KING:

For example, I want to search for value KING in all columns of all the tables in entire SCOTT schema:

SQL> variable val varchar2(10)
SQL> exec :val := 'KING'

PL/SQL procedure successfully completed.

SQL> SELECT DISTINCT SUBSTR (:val, 1, 11) "Searchword",
  2    SUBSTR (table_name, 1, 14) "Table",
  3    SUBSTR (column_name, 1, 14) "Column"
  4  FROM cols,
  5    TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select '
  6    || column_name
  7    || ' from '
  8    || table_name
  9    || ' where upper('
 10    || column_name
 11    || ') like upper(''%'
 12    || :val
 13    || '%'')' ).extract ('ROWSET/ROW/*') ) ) t
 14  ORDER BY "Table"
 15  /

Searchword  Table          Column
----------- -------------- --------------
KING        EMP            ENAME

这篇关于查询以在数据库的所有表中查找值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！