报价:

我已经在下面尝试使用此语句 根据以下内容找到合适的列 我认为应该命名，但是它 没有返回结果.*

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'
     ...
     ;