我想在oracle 11g中所有表的所有列中搜索特定值 [英] I want Search specific value in all columns of all tables in oracle 11g

问题描述

是否可以在每个表的每个字段中搜索Oracle中的特定值.

Is it possible to search every field of every table for a particular value in Oracle.

我想不使用任何程序来做..

I want to do it without using any procedure..

我们可以通过查询吗?

推荐答案

尽管有点麻烦，但您可以通过单个查询来完成.该查询将在当前模式中的所有CHAR和VARCHAR2列中搜索字符串'JONES'

You can do it with a single query though it's a bit convoluted. This query will search all CHAR and VARCHAR2 columns in the current schema for the string 'JONES'

select table_name,
       column_name
  from( select table_name,
               column_name,
               to_number(
                 extractvalue(
                   xmltype(
                     dbms_xmlgen.getxml(
                       'select count(*) c from ' || table_name ||
                       ' where to_char(' || column_name || ') = ''JONES'''
                     )
                   ),
                   'ROWSET/ROW/C'
                 )
               ) cnt
          from (select utc.*, rownum
                  from user_tab_columns utc
                 where data_type in ('CHAR', 'VARCHAR2') ) )
 where cnt >= 0

请注意，这是Laurent Schneider对

Note that this is an adapted version of the Laurent Schneider's query to count the rows in every table with a single query.

这篇关于我想在oracle 11g中所有表的所有列中搜索特定值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！