Искать в базе данных Oracle таблицы с конкретными именами столбцов?

74

У нас есть большая база данных Oracle со многими таблицами. Есть ли способ, которым я могу выполнить запрос или поиск, чтобы найти, есть ли таблицы с определенными именами столбцов?

IE показать мне все таблицы, которые имеют столбцы: id, fname, lname, address

Подробно Я забыл добавить: мне нужно иметь возможность выполнять поиск по различным схемам. Тот, который я должен использовать для подключения, не имеет собственных таблиц, которые мне нужно выполнить.

Теги:

5 ответов

139
Лучший ответ

Чтобы найти все таблицы с определенным столбцом:

select owner, table_name from all_tab_columns where column_name = 'ID';

Чтобы найти таблицы, в которых есть все или все из 4 столбцов:

select owner, table_name, column_name
from all_tab_columns
where column_name in ('ID', 'FNAME', 'LNAME', 'ADDRESS');

Чтобы найти таблицы, в которых есть все 4 столбца (без них отсутствует):

select owner, table_name
from all_tab_columns
where column_name in ('ID', 'FNAME', 'LNAME', 'ADDRESS')
group by owner, table_name
having count(*) = 4;
Поделиться
  • 2
    Вам, вероятно, следует использовать DBA_TAB_COLUMNS вместо ALL_TAB_COLUMNS для выполнения этого поиска, в случае, если пользователь, в который вы входите, не имеет доступа к некоторым таблицам.
  • 0
    True, но только если пользователь, к которому вы подключены, имеет привилегию SELECT ANY TABLE.
Показать ещё 1 комментарий
9

Данные, которые вы хотите, находятся в таблице метаданных "cols":

SELECT * FROM COLS WHERE COLUMN_NAME = 'id'

Это даст вам список таблиц, в которых есть все нужные столбцы:

select distinct
  C1.TABLE_NAME
from
  cols c1
  inner join
  cols c2
  on C1.TABLE_NAME = C2.TABLE_NAME
  inner join
  cols c3
  on C2.TABLE_NAME = C3.TABLE_NAME
  inner join
  cols c4
  on C3.TABLE_NAME = C4.TABLE_NAME  
  inner join
  tab t
  on T.TNAME = C1.TABLE_NAME
where T.TABTYPE = 'TABLE' --could be 'VIEW' if you wanted
  and upper(C1.COLUMN_NAME) like upper('%id%')
  and upper(C2.COLUMN_NAME) like upper('%fname%')
  and upper(C3.COLUMN_NAME) like upper('%lname%')
  and upper(C4.COLUMN_NAME) like upper('%address%')

Чтобы сделать это в другой схеме, просто укажите схему перед таблицей, как в

SELECT * FROM SCHEMA1.COLS WHERE COLUMN_NAME LIKE '%ID%';

Если вы хотите совместить поиск многих схем с одним результатом вывода, вы можете сделать это:

SELECT DISTINCT
  'SCHEMA1' AS SCHEMA_NAME
 ,TABLE_NAME
FROM SCHEMA1.COLS
WHERE COLUMN_NAME LIKE '%ID%'
UNION
SELECT DISTINCT
  'SCHEMA2' AS SCHEMA_NAME
 ,TABLE_NAME
FROM SCHEMA2.COLS
WHERE COLUMN_NAME LIKE '%ID%'
Поделиться
  • 0
    Как я могу использовать это, чтобы посмотреть на другую схему? (Я забыл упомянуть это требование в моем первоначальном вопросе)
  • 0
    Просто добавьте имя схемы в начало каждого имени таблицы ... т.е. myschema.c1. Очевидно, вы должны иметь привилегии выбора в другой схеме
Показать ещё 4 комментария
8

Чтобы найти имя столбца, используйте приведенный ниже запрос, если вы точно знаете имя столбца:

select owner,table_name from all_tab_columns where upper(column_name) =upper('keyword');

Чтобы найти имя столбца, если вы не знаете точное использование столбцов ниже:

select owner,table_name from all_tab_columns where upper(column_name) like upper('%keyword%');
Поделиться
  • 1
    upper трюк - грт.
1

выберите table_name из dba_tab_columns где column_name = 'THE_COLUMN_YOU_LOOK_FOR';

Поделиться
0

Вот один из них, который мы сохранили для findcol.sql, чтобы мы могли легко запускать его из SQLPlus

set verify off
clear break
accept colnam prompt 'Enter Column Name (or part of): '
set wrap off
select distinct table_name, 
                column_name, 
                data_type || ' (' || 
                decode(data_type,'LONG',null,'LONG RAW',null,
                       'BLOB',null,'CLOB',null,'NUMBER',
                       decode(data_precision,null,to_char(data_length),
                              data_precision||','||data_scale
                             ), data_length
                      ) || ')' data_type
  from all_tab_columns
 where column_name like ('%' || upper('&colnam') || '%');
set verify on
Поделиться

Ещё вопросы

Сообщество Overcoder
Наверх
Меню