Язык определения данных (SQL DDL)

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

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

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

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

Стандартное

Консоль

Выполнить

SELECT table_name FROM information_schema.TABLES;

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';

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';

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';

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');

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');