在所有表中的所有字段中搜索特定值 (Oracle) [英] Search All Fields In All Tables For A Specific Value (Oracle)

问题描述

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

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

某些表中有数百个表和数千行，所以我知道这可能需要很长时间来查询.但我唯一知道的是，我要查询的字段的值是 1/22/2008P09RR8.<

There are hundreds of tables with thousands of rows in some tables so I know this could take a very long time to query. But the only thing I know is that a value for the field I would like to query against is 1/22/2008P09RR8. <

我已经尝试使用下面的这个语句根据我认为应该命名的内容找到合适的列，但没有返回任何结果.

I've tried using this statement below to find an appropriate column based on what I think it should be named but it returned no results.

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

绝对没有关于这个数据库的文档，我不知道这个字段是从哪里提取的.

There is absolutely no documentation on this database and I have no idea where this field is being pulled from.

有什么想法吗?

推荐答案

引用:

我试过在下面使用这个语句找到一个合适的列基于我认为它应该被命名，但它没有返回结果.*

I've tried using this statement below to find an appropriate column based on what I think it should be named but it returned no results.*

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

列不是对象.如果您的意思是希望列名类似于%DTN%"，那么您想要的查询是:

A column isn't an object. If you mean that you expect the column name to be like '%DTN%', the query you want is:

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

但如果DTN"字符串只是您的猜测，那可能无济于事.

But if the 'DTN' string is just a guess on your part, that probably won't help.

顺便说一下，您有多确定1/22/2008P09RR8"是直接从单列中选择的值?如果您根本不知道它来自哪里，它可能是几个列的串联，或者某个函数的结果，或者嵌套表对象中的一个值.因此，您可能正在疯狂地尝试检查每一列的值.您不能从显示此值的任何客户端应用程序开始并尝试找出它使用什么查询来获取它吗?

By the way, how certain are you that '1/22/2008P09RR8' is a value selected directly from a single column? If you don't know at all where it is coming from, it could be a concatenation of several columns, or the result of some function, or a value sitting in a nested table object. So you might be on a wild goose chase trying to check every column for that value. Can you not start with whatever client application is displaying this value and try to figure out what query it is using to obtain it?

无论如何，diciu 的回答提供了一种生成 SQL 查询以检查每个表的每一列的值的方法.您还可以使用 PL/SQL 块和动态 SQL 在一个 SQL 会话中完全执行类似的操作.这是一些匆忙编写的代码:

Anyway, diciu's answer gives one method of generating SQL queries to check every column of every table for the value. You can also do similar stuff entirely in one SQL session using a PL/SQL block and dynamic SQL. Here's some hastily-written code for that:

    SET SERVEROUTPUT ON SIZE 100000

    DECLARE
      match_count INTEGER;
    BEGIN
      FOR t IN (SELECT owner, table_name, column_name
                  FROM all_tab_columns
                  WHERE owner <> 'SYS' and data_type LIKE '%CHAR%') LOOP

        EXECUTE IMMEDIATE
          'SELECT COUNT(*) FROM ' || t.owner || '.' || t.table_name ||
          ' WHERE '||t.column_name||' = :1'
          INTO match_count
          USING '1/22/2008P09RR8';

        IF match_count > 0 THEN
          dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
        END IF;

      END LOOP;

    END;
    /

您也可以通过一些方法提高效率.

There are some ways you could make it more efficient too.

在这种情况下，根据您要查找的值，您可以清楚地消除任何 NUMBER 或 DATE 类型的列，这将减少查询次数.甚至可能将其限制为类型类似于%CHAR%"的列.

In this case, given the value you are looking for, you can clearly eliminate any column that is of NUMBER or DATE type, which would reduce the number of queries. Maybe even restrict it to columns where type is like '%CHAR%'.

您可以像这样为每个表构建一个查询，而不是每列一个查询:

Instead of one query per column, you could build one query per table like this:

SELECT * FROM table1
  WHERE column1 = 'value'
     OR column2 = 'value'
     OR column3 = 'value'
     ...
     ;

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