INFORMATION_SCHEMA и Oracle

Информационная схема (INFORMATION_SCHEMA) является стандартным представлением метаданных в языке SQL. Исторически каждый производитель реляционных СУБД предоставлял системные таблицы, которые содержали мета-информацию - имена таблиц, столбцов, ограничений, типы данных столбцов и т.д. Структура и состав системных таблиц могут меняться в разных версиях продукта, однако поддержка информационной схемы дает возможность менять структуру системных таблиц без изменения способа доступа к метаданным. Другим преимуществом применения INFORMATION_SCHEMA является то, что запросы к метаданным не зависят от используемой СУБД.

Из ведущих производителей, пожалуй, только Oracle не поддерживает INFORMATION_SCHEMA. Справедливости ради следует сказать, что Oracle предоставляет возможность использовать системные представления вместо непосредственного обращения к системным таблицам, что также позволяет безопасно изменять структуру системных таблиц.

Приведем ряд типичных запросов в стандартном варианте и для Oracle.

1. Список таблиц базы данных (текущей)

Стандартное

select table_name from information_schema.tables;

Oracle

select table_name from all_tables where owner='ИМЯ СХЕМЫ';

2. Список столбцов заданной таблицы (например, PC)

Стандартное

select column_name from information_schema.columns where table_name='PC';

Oracle

select column_name from all_tab_columns where table_name='PC';

3. Тип данных и размер заданного символьного столбца таблицы (столбец model таблицы PC)

Стандартное

select column_name, data_type, CHARACTER_MAXIMUM_LENGTH
from information_schema.columns where table_name='PC' and column_name = 'model';

Oracle

select column_name,data_type,data_length from all_tab_columns
where table_name='PC'and column_name='MODEL';

4. Имя ограничения первичного ключа таблицы PC

Стандартное

select table_name,constraint_name  from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where table_name='PC' and CONSTRAINT_TYPE='Primary key';

Oracle

select table_name,constraint_name from all_constraints
where table_name='PC' and constraint_type='P';

5. Список столбцов, составляющих первичный ключ таблицы Utb

Стандартное

select table_name, column_name
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where table_name='utb'
and constraint_name=(select constraint_name from INFORMATION_SCHEMA.table_constraints
where table_name='utb' and CONSTRAINT_TYPE='Primary key');

Здесь мы в основном запросе получаем столбцы, участвующие в ограничениях, и выбираем те из них, у которых имя ограничения совпадает с именем ограничения первичного ключа для той же таблицы. В принципе, условие table_name=‘utb’ основного запроса является избыточным с точки зрения получения правильного ответа, поскольку имя ограничения является уникальным для всей базы. Однако ранняя фильтрация существенно ускоряет выполнение запроса.

Оракл

select table_name, column_name from all_ind_columns where table_name='UTB'
and index_name=(select constraint_name from all_constraints
where table_name='UTB' and constraint_type='P');