oracle-запрос для извлечения информации из словаря данных, включая таблицу первичного ключа и столбец, на который указывает этот внешний ключ

Question

Мне нужно создать запрос в Oracle 10g для словаря данных, который извлекает следующую информацию для всех таблиц в данной схеме:

1. Имя таблицы
2. Название колонки
3. "P", если столбец является первичным ключом
4. Если на этот столбец ссылаются во внешнем ключе, имя таблицы + '.' + имя столбца первичного ключа в другой таблице, на которую указывает этот внешний ключ.
5. Тип данных столбца

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

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

    SELECT DISTINCT
     t.table_name,
     c.column_name,
     (SELECT con.constraint_type
        FROM all_constraints con, all_cons_columns cols
       WHERE     con.constraint_name = cols.constraint_name
             AND con.owner = cols.owner
             AND t.table_name = cols.table_name
             AND c.column_name = cols.column_name
             AND con.constraint_type = 'P'      --  AND status = 'enabled'
                                          )
        AS pk_constraint,
     (SELECT (SELECT conc.table_name || '.' || conc.column_name
                FROM all_cons_columns conc
               WHERE conc.owner = con.owner
                     AND constraint_name = con.r_constraint_name)
                fk_ref
        FROM all_constraints con, all_cons_columns col
       WHERE     con.owner = col.owner
             AND con.table_name = t.table_name
             AND con.constraint_name = col.constraint_name
             AND con.constraint_type = 'R')
        AS fk_constraint,
     c.data_type,
     d.comments
FROM all_tables t, all_tab_columns c, all_col_comments d
WHERE     t.table_name = c.table_name
     AND t.owner = c.owner
     AND t.table_name = d.table_name
     AND t.owner = d.owner
     AND t.owner = %MY_SCHEMA_NAME%
     AND t.table_name = %MY_TABLE_NAME%
ORDER BY t.table_name, c.column_name;

Вот что должно быть возвращено (пример данных - при условии, что CustomerAddress.State содержит код, который ссылается на таблицу State, содержащую список кодов состояний с первичным ключом State.StateCode):

• CustomerAddress AddressID P NULL NUMBER
• КлиентАдрес УлицаАдрес NULL NULL VARCHAR (2)
• CustomerAddress State NULL State.StateCode CHAR

Это то, что на самом деле возвращает мой запрос. Вы можете видеть, что State.StateCode повторяется в каждой строке. Это должно появиться только в последнем ряду.

• CustomerAddress AddressID P State.StateCode NUMBER
• CustomerAddress StreetAddress NULL State.StateCode VARCHAR (2)
• CustomerAddress State NULL State.StateCode CHAR

Спасибо за чтение!

Answer 1

Похоже, что на сайте StackExchange есть ответ, который вы ищете по этой ссылке.

Ответ, основанный на Frosty Code, делает то, что вам нужно, и, немного поэкспериментировав, вы получите желаемые результаты.