29.11.2010 Список ссылающихся таблиц

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

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

SELECT table_name
FROM user_constraints
WHERE table_name='MY_TABLE'
AND constraint_type='P'

Далее воспользовавшись тем, что в системном представлении USER_CONSTRAINTS есть поле R_CONSTRAINT_NAME, показывающее для ограничения с типом R имя ограничения-ссылки на другую таблицу, а также предыдущим запросом, мы можем написать следующий запрос:

SELECT DISTINCT TABLE_NAME
FROM USER_CONSTRAINTS
WHERE R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME='MY_TABLE'
AND CONSTRAINT_TYPE='P')
ORDER BY TABLE_NAME

В этом запросе мы получаем уникальный список ссылающихся таблиц на заданную таблицу с именем MY_TABLE, вместо которого вы должны подставить имя вашей таблицы.

Можно несколько модифицировать наш запрос и вместе с именем таблицы выводить также комментарии к таблице, что может быть очень удобно:

SELECT DISTINCT C.TABLE_NAME,
T.COMMENTS
FROM USER_CONSTRAINTS C,
USER_TAB_COMMENTS T
WHERE T.TABLE_NAME=C.TABLE_NAME
AND C.R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME='MY_TABLE'
AND CONSTRAINT_TYPE='P')
ORDER BY C.TABLE_NAME

Естественно, что эти запросы должны выполняться под именем пользователя - владельца схемы с таблицами вашего приложения, так как в этом примере использовано системное представление из группы системных представлений USER_ для текущего пользователя. Для большей универсальности можно использовать системные представления из группы DBA_, но в этом случае необходимо иметь соответствующие права для работы с этими представлениями. В этом случае наш последний запрос будет выглядеть следующим образом:

SELECT DISTINCT C.TABLE_NAME,
T.COMMENTS
FROM DBA_CONSTRAINTS C,
DBA_TAB_COMMENTS T
WHERE T.TABLE_NAME=C.TABLE_NAME
AND C.R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME
FROM DBA_CONSTRAINTS
WHERE TABLE_NAME='MY_TABLE'
AND CONSTRAINT_TYPE='P')
ORDER BY C.TABLE_NAME

www.foxbase.ru