Поиск таблиц по именам столбцов в базе Oracle

Быстрый ответ

Чтобы быстро найти таблицы в Oracle с конкретным столбцом, воспользуйтесь следующим запросом:

SELECT table_name FROM all_tab_columns WHERE column_name = 'НАЗВАНИЕ_СТОЛБЦА';

Замените 'НАЗВАНИЕ_СТОЛБЦА' на искомое имя столбца в верхнем регистре. В результате вы получите все таблицы, содержащие указанный столбец.

Поиск нескольких столбцов одновременно

Если вам нужно найти таблицы, содержащие сразу несколько столбцов, например 'COL1', 'COL2', 'COL3' и 'COL4', и при этом важно, чтобы в таблице присутствовали все эти столбцы, используйте этот запрос:

-- SQL-аналог "фулл-хауса" из покера
SELECT owner, table_name 
FROM all_tab_columns 
WHERE column_name IN ('COL1', 'COL2', 'COL3', 'COL4') 
GROUP BY owner, table_name 
HAVING COUNT(DISTINCT column_name) = 4;

Учёт регистра при поиске: функция Upper

Oracle обычно хранит имена столбцов в верхнем регистре, но если вам нужно учесть имена столбцов в разном регистре, можно использовать функцию UPPER для регистронезависимого сравнения.

-- Oracle умеет только 'COL1', а где 'Col1'?
SELECT owner, table_name 
FROM all_tab_columns 
WHERE UPPER(column_name) IN (UPPER('Col1'), UPPER('Col2'));

Учитывайте поле owner для различения схем.

Доступ к представлениям и поиск по частичному совпадению имен: оператор LIKE

Для более широкого поиска, включающего частичное совпадение имён столбцов, можно использовать оператор LIKE в сочетании с символом %, заменяющим любое количество символов.

-- В поисках частей имён столбцов!
SELECT owner, table_name FROM all_tab_columns WHERE column_name LIKE '%ЧАСТЬ_НАЗВАНИЯ%';

Чтобы исключить повторения, используйте SELECT DISTINCT.

Обширный поиск: оператор UNION

С помощью оператора UNION можно объединить результаты нескольких запросов SELECT. Так, можно расширять область поиска, включая в него представления Oracle.

-- Супергеройский поиск в метаданных!
SELECT 'Table' AS Type, owner, table_name
FROM all_tab_columns WHERE column_name = 'НАЗВАНИЕ_СТОЛБЦА'
UNION
SELECT 'View', owner, view_name
FROM all_views WHERE column_name = 'НАЗВАНИЕ_СТОЛБЦА';

Теперь вы обнаружите не только таблицы, но и представления с искомым столбцом.

Визуализация

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

"Ищу таблицы со столбцами 'ПОДОЗРЕВАЕМЫЙ' и 'МЕСТОПОЛОЖЕНИЕ'."

Сформулируем запрос:

SELECT table_name
FROM all_tab_columns
WHERE column_name IN ('ПОДОЗРЕВАЕМЫЙ', 'МЕСТОПОЛОЖЕНИЕ');

И вот результаты:

📚 Найденные таблицы: 📚
| Название таблицы    | Столбцы                  |
| -------------------- | ------------------------ |
| Места_Преступлений  | 'подозреваемый', 'местоположение' |
| Алиби               | 'подозреваемый'          |
| Убежища             | 'местоположение'         |

Каждое имя таблицы – свидетельство, которое может помочь нам сделать следующее открытие! 🕵️‍♂️✨

Изучение метаданных: типы данных и размеры

Не только имена столбцов могут быть важны, но и их типы данных, рядом с их размерами. Для отображения всей необходимой информации применяем следующий запрос:

-- Раскрываем все тайны столбцов!
SELECT owner, table_name, column_name, data_type, data_length
FROM all_tab_columns
WHERE column_name = 'НАЗВАНИЕ_СТОЛБЦА';

Этот запрос выдаст полные сведения о столбцах, которые вас интересуют.

Использование различных представлений метаданных

Oracle имеет разные представления метаданных, такие как dba_tab_columns или user_tab_columns, обеспечивающие различные подходы к данным, в зависимости от доступных прав.

• DBA_TAB_COLUMNS доступно лишь привилегированным пользователям и предоставляет системный обзор.
• USER_TAB_COLUMNS показывает таблицы, принадлежащие текущему пользователю.

Объединённый поиск для получения уникальных результатов

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

-- Все за одного и один за всех.
SELECT DISTINCT owner, table_name 
FROM all_tab_columns 
WHERE column_name = 'НАЗВАНИЕ_СТОЛБЦА' 
ORDER BY owner, table_name;

Этот запрос предоставит вам уникальный упорядоченный список таблиц из всех схем.

Полезные материалы

1. DBA_TAB_COLUMNS — документация Oracle.
2. Список всех таблиц в Oracle – Stack Overflow.
3. Поиск таблиц с определённым именем столбца – Oracle FAQ.
4. Oracle Вопросы – find_column.php – Tech on the Net.
5. Поиск по всем столбцам всех таблиц – Ask TOM.