从架构中查找列和值扫描oracle中的所有表 [英] Find a column and a value from a schema scan all the table in oracle

问题描述

我正在尝试从架构中查找列(ABC)及其值1234，基本上我需要检查 ABC 以及此列 1234中的值存在于映射到 ABC 的任何其他表中，我试图以最有效的方式进行搜索，但是这会花费大量时间，而且无法获取所需的结果

I am trying to find a column (ABC) and it's value 1234 from a schema , basically i need to to check if ABC and a value from this column 1234 is present in any other table that is mapped to ABC , i tried to do a search the most efficient way but it is taking lot of time and not retrieving the desired result

我尝试过 https://lalitkumarb.wordpress.com/2015/01/06/sql-to-search-for-a-value-in-all-columns-of-all-atbles -in-an-entire-schema/

但是查询根本不是结果，而是正在运行...

but the query is not results at all it is running running...

推荐答案

如果在set Serveroutput上发生缓冲区溢出，则可以将输出写入文件，否则应该这样做.输出将包含所有具有'ABC'列的表并且相应的计数显示ABC列值为1234的记录计数.

You may write the output to a file if you get buffer overflow on set Serveroutput otherwise this should do.Output will have all tables that has 'ABC' column and respective count shows count of record with ABC column value as 1234.

SET SERVEROUTPUT ON 100000
DECLARE 
lv_count number(10):=0;
l_str    varchar2 (1000);
BEGIN 
FOR V1 IN 
(select distinct table_name 
 from dba_tab_columns 
 where column_name = 'ABC')

 LOOP

  BEGIN 
    lv_query := ' select count(*) from '||v1.table_name||' where ABC =1234';
    EXECUTE IMMEDIATE lv_query INTO lv_count;
    dbms_output.put_line(v1.table_name||' --> '||lv_count);

    EXCEPTION 
       WHEN OTHERS THEN 
         dbms_output.put_line('OTHERS EXCEPTION '||v1.table_name||' ERRCODE '||SQLERRM||' '||SUBSTR(SQLCODE,1,200));
    END; 

 END LOOP;

END;

要查找所有具有column_name ABC的表，应执行以下简单查询.

To find all tables having column_name ABC, simple query as below should do.

select table_name 
     from dba_tab_columns 
     where column_name = UPPER('ABC');

PS:元数据表(dba_Tab_columns)以大写形式存储column_name，以避免出现大小写问题，将大小写转换为大写.

PS: Metadata tables(dba_Tab_columns) stores column_name in upper case, to avoid any issues with case ,converting the case to upper for the literal.

PL/SQL块中的第二个查询

Second query in PL/SQL block,

 SET SERVEROUTPUT ON 100000
    DECLARE 
    lv_count number(10):=0;
    l_str    varchar2 (1000);
    lv_col_name varchar2(255) :='ABC';

    BEGIN 
    FOR V1 IN 
    (select distinct table_name 
     from dba_tab_columns 
     where column_name = lv_col_name)

     LOOP
      dbms_output.put_line(lv_col_name||' '||v1.table_name);    
     END LOOP;

    END;

这篇关于从架构中查找列和值扫描oracle中的所有表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！