Achar tabelas que tenha a informação desejada

[jhonnygabi]

Tenho um sistema com 640 tabelas.
Quero encontrar a tabela que tenha os tipos de venda cadastradas:
ex: bonificação, brinde...
Quero saber os nomes das tabelas que tenha essas informações gravadas.

[diegolenhardt]

teria que fazer um loop na user_tab_columns e sair montando consultas dinamicas com execute immediate.

[fsitja]

Eu recomendaria um bom e velho diagrama ER. Chama o AD para um papo.

Se isso não for possível você vai suar a camisa e ainda assim pode não achar algo. Se suas tabelas tiverem comentários nas colunas fica mais simples... mas vai ter que testar... pode contar abreviatura das palavras, pode ter sido escrito errado, pode ter acento, cedilha, etc... Eu usaria expressões regulares para facilitar isso.

No exemplo abaixo estou buscando pelas palavras YES, MAXIMUM e NAME no comentário da coluna e nos nomes das colunas em si.

Selecionar tudo

select tc.owner, tc.table_name, tc.column_name, cc.comments, tc.column_name || ' - ' || cc.comments name_comment
  from all_tab_cols tc 
  left join all_col_comments cc on tc.owner = cc.owner
                             and tc.table_name = cc.table_name
                             and tc.column_name = cc.column_name
 where regexp_instr(upper(tc.column_name || ' - ' || cc.comments), '.YES.') > 0
    or regexp_instr(upper(tc.column_name || ' - ' || cc.comments), '.MAXIMUM.') > 0
    or regexp_instr(upper(tc.column_name || ' - ' || cc.comments), '.NAME.') > 0

[victorhugomuniz]

você tem q ter mais alguma informacao?

quando preciso de um objeto no banco e tenho/sei muito pouco sobre este objeto eu faco isso:

Selecionar tudo

select * from all_source where text like '%tenho_isso%'

[jhonnygabi]

O QUE PRECISO É MAIS OU MENOS ISSO:

tenho a seguinte linha de comando:

Selecionar tudo

SELECT *
FROM all_tab_columns
WHERE upper(column_name) LIKE 'SENHA';

Essa linha me diz todas as tabelas que tenha a coluna chamada "senha"...

Preciso de algo parecido que me diga quais colunas de quais tabelas tenha uma determinada informação gravada.

ex: quando bloqueio o cliente, tenho de dar o motivo do bloquei (ex: cliente inativo). Quero saber qual tabela tem esse texto armazenado dentro de sí.

[burga]

Mas existe um nome fixo pra coluna aonde buscar a informação? Ou pode ser coluna de qualquer nome?

Montei um pequeno procedimento, mas pelo tamanho da sua base e dependendo de indices, tipos da coluna etc, esse procedimento pode demorar pra terminar de rodar.

Ele busca por colunas de qualquer nome, você só precisa informar os possíveis tipos pra coluna e os owners no select do "FOR" e também informa a sentença buscada na vatriável "string" localizada logo após o begin. Lembrando que quanto mais restrições melhor o desempenho!

Selecionar tudo

declare
  verifica varchar2(4000);
  string varchar2(4000);
begin
  string := upper('KING');
  for i in (select owner, table_name, column_name 
            from all_tab_columns 
            where data_type in ('VARCHAR2','CHAR','VARCHAR','NCHAR','NVARCHAR2') 
            and owner in ('HR','SCOTT'))
  loop
    verifica := null;
    begin 
      execute immediate 'select substr(' || i.column_name || ',1,4000) from ' || i.owner || '.' || i.table_name || ' where upper(' || i.column_name || ') like ''%' || string || '%'' and rownum = 1' into verifica;
      exception 
        when no_data_found then
        null;
      end;
    if (verifica is not null) then
      dbms_output.put_line('OWNER: ' || i.owner || CHR(10) || '  - TABELA: ' || i.table_name || CHR(10) || '  - COLUNA: ' || i.column_name || CHR(10));
    end if;
  end loop;
end;
/