Jak mogę znaleźć, które tabele odnoszą się do danej tabeli w Oracle SQL Developer?


199

W Oracle SQL Developer , jeśli przeglądam informacje w tabeli, mogę zobaczyć ograniczenia, które pozwalają mi zobaczyć klucze obce (a tym samym, do których tabel odwołuje się ta tabela), i mogę zobaczyć zależności, aby zobaczyć, co pakiety i takie odniesienia do tabeli. Ale nie jestem pewien, jak znaleźć, które tabele odnoszą się do tabeli.

Powiedzmy, że patrzę na empstół. Istnieje inna tabela, emp_deptktóra przechwytuje, którzy pracownicy pracują, w których działach, która odwołuje się do emptabeli emp_id, główny klucz emptabeli. Czy istnieje sposób (poprzez jakiś element interfejsu użytkownika w programie, a nie przez SQL), aby stwierdzić, że emp_depttabela odwołuje się do emptabeli, bez mojej wiedzy, że emp_depttabela istnieje?

Odpowiedzi:


258

Nie. Nie ma takiej opcji dostępnej w Oracle SQL Developer.

Musisz wykonać zapytanie ręcznie lub użyć innego narzędzia (na przykład PLSQL Developer ma taką opcję). Poniższy kod SQL jest używany przez programistę PLSQL:

select table_name, constraint_name, status, owner
from all_constraints
where r_owner = :r_owner
and constraint_type = 'R'
and r_constraint_name in
 (
   select constraint_name from all_constraints
   where constraint_type in ('P', 'U')
   and table_name = :r_table_name
   and owner = :r_owner
 )
order by table_name, constraint_name

Gdzie r_ownerjest schemat i r_table_nametabela, do której szukasz odniesień. W nazwach rozróżniana jest wielkość liter


Zachowaj ostrożność, ponieważ na karcie raportów Oracle SQL Developer znajduje się opcja „Wszystkie tabele / Zależności” z ALL_DEPENDENCIES, która odnosi się do „ zależności między procedurami, pakietami, funkcjami, treściami pakietów i wyzwalaczami dostępnymi dla bieżącego użytkownika, w tym zależności w widokach utworzonych bez żadnych linków do bazy danych ”. . Następnie ten raport nie ma wartości dla twojego pytania.


30
Dziękuję za odpowiedź. Wstydź się Oracle Sql Developer za ssanie.
Greg

1
Wspomniałeś, że PLSQL Developer może wykonywać tę funkcję, czy możesz wyjaśnić, w jaki sposób?
Nicholas

4
@Nicholas, W przeglądarce obiektów wybierz tabelę, kliknij tabelę prawym przyciskiem myszy i wybierz „Odwołania do klucza obcego”
FerranB

3
Ta odpowiedź wskazuje, że SQL Developer 4.1 i nowsze mają teraz opcję zakładki „Model”, która pokaże te informacje w formacie ERD.
SnoringFrog

1
r_owner to schemat, którego używasz, r_table_name to tabela, do której szukasz odniesień
Flowy

108

Aby dodać to do SQL Developer jako rozszerzenie, wykonaj następujące czynności:

  1. Zapisz poniższy kod w pliku xml (np. Fk_ref.xml):
<items>
    <item type="editor" node="TableNode" vertical="true">
    <title><![CDATA[FK References]]></title>
    <query>
        <sql>
            <![CDATA[select a.owner,
                            a.table_name,
                            a.constraint_name,
                            a.status
                     from   all_constraints a
                     where  a.constraint_type = 'R'
                            and exists(
                               select 1
                               from   all_constraints
                               where  constraint_name=a.r_constraint_name
                                      and constraint_type in ('P', 'U')
                                      and table_name = :OBJECT_NAME
                                      and owner = :OBJECT_OWNER)
                               order by table_name, constraint_name]]>
        </sql>
    </query>
    </item>
</items>
  1. Dodaj rozszerzenie do SQL Developer:

    • Narzędzia> Preferencje
    • Baza danych> Rozszerzenia zdefiniowane przez użytkownika
    • Kliknij przycisk „Dodaj wiersz”
    • W Typ wybierz „EDYTOR”, Lokalizacja to miejsce, w którym zapisałeś plik xml powyżej
    • Kliknij „OK”, a następnie uruchom ponownie programistę SQL
  2. Przejdź do dowolnej tabeli, a obok tabeli SQL powinna pojawić się dodatkowa karta, oznaczona jako Odwołania FK, która wyświetla nowe informacje o FK.

  3. Odniesienie


Czy wiesz, jaka jest nazwa węzła dla pakietów? Wszystkie linki xsd, które znajduję w sieci, nie są już prawidłowe (jak w Oracle je usunęły).
James Sumners

1
Dodałem niewielką zmianę do Twojej sugestii: a właściciel = użytkownik przed złożeniem zamówienia, tak że jeśli masz dwie instancje tych samych tabel w dwóch schematach, otrzymujesz tylko referencje dotyczące twojego schematu
użytkownik1708042

Dodałem ten warunek: and owner = :OBJECT_OWNERwcześniej and exists.
M Denis,

3
@ M-Denis, w tym przypadku możesz pominąć odniesienia z innych schematów.
Youw

po zastosowaniu tego i uruchomieniu describe books;i select * from books;nie wyświetla karty odnośników fk na maszynie wirtualnej dewelopera Oracle SQL.
mLstudent33

36

Zamień [Your TABLE] na emp w zapytaniu poniżej

select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name
  from all_constraints 
 where constraint_type='R'
   and r_constraint_name in (select constraint_name 
                               from all_constraints 
                              where constraint_type in ('P','U') 
                                and table_name='[YOUR TABLE]');

Myślę, że constraint_type in ('P','U') jest zbyteczny, ponieważ jeśli typ ograniczenia TOTO ograniczenia to „R”, to nazwa ograniczenia R r TOTO jest oczywiście nazwą ograniczenia typu „P” LUB „U” w tabeli, do której się odwołuje. Nie ma potrzeby tego określać. Używasz IN, więc jest tak jak wiele, ORa my troszczymy się tylko o jedyny operand z OR, który daje prawdę.
Gab 是 好人

10

Możesz wyświetlić zapytanie z tego ALL_CONSTRAINTSwidoku:

SELECT table_name
FROM ALL_CONSTRAINTS
WHERE constraint_type = 'R' -- "Referential integrity"
  AND r_constraint_name IN
    ( SELECT constraint_name
      FROM ALL_CONSTRAINTS
      WHERE table_name = 'EMP'
        AND constraint_type IN ('U', 'P') -- "Unique" or "Primary key"
    );

1
Klucze obce mogą odnosić się do kluczy unikatowych, nie tylko kluczy podstawowych, a nazwa tabeli może być używana w wielu schematach, co skutkuje wieloma dopasowaniami. Musisz także użyć kolumny „Właściciel”, jeśli zamierzasz użyć „Ograniczeń wszystkich”, a nie „Ograniczeń użytkownika”.
Mark Roddy

Dzięki za skomentowanie, czym są „R” „U” i „P”
Jeff

Nie zapomnij o średniku na końcu żądania SQL.
Gab 是 好人

Nawiasem mówiąc, typ ograniczenia w („P”, „U”) jest zbędny, ponieważ jeśli typ ograniczenia TOTO ograniczenia to „R”, wówczas nazwa r_constraint_ TOTO jest oczywiście nazwą ograniczenia typu „P” LUB „U” ”w przywołanej tabeli. Nie ma potrzeby tego określać.
Gab 是 好人

9

SQL Developer 4.1, wydany w maju 2015 r., Dodał kartę Model, która pokazuje klucze obce tabeli, które odnoszą się do twojej tabeli w formacie diagramu relacji encji.


1
Mniej przydatne, jeśli z jakiegoś powodu potrzebujesz tego w skrypcie, ale jeśli potrzebujesz tylko wiedzieć o połączeniach, wydaje się to nowoczesną drogą.
SnoringFrog

1
@SnoringFrog, technicznie rzecz biorąc, pytanie wymaga elementu interfejsu użytkownika, więc jest to najbardziej odpowiednia odpowiedź
WhatsThePoint

4

Co powiesz na coś takiego:

SELECT c.constraint_name, c.constraint_type, c2.constraint_name, c2.constraint_type, c2.table_name
  FROM dba_constraints c JOIN dba_constraints c2 ON (c.r_constraint_name = c2.constraint_name)
 WHERE c.table_name = <TABLE_OF_INTEREST>
   AND c.constraint_TYPE = 'R';

2
To działało dla mnie, kiedy zmieniłem nazwę tabeli z dba_constraintsna all_constraintspodobną:SELECT c.constraint_name, c.constraint_type, c2.constraint_name, c2.constraint_type, c2.table_name FROM all_constraints c JOIN all_constraints c2 ON (c.r_constraint_name = c2.constraint_name) WHERE c.table_name = '<TABLE_OF_INTEREST>' AND c.constraint_TYPE = 'R';
chrisjleu

4
SELECT DISTINCT table_name, 
                constraint_name, 
                column_name, 
                r_table_name, 
                position, 
                constraint_type 
FROM   (SELECT uc.table_name, 
               uc.constraint_name, 
               cols.column_name, 
               (SELECT table_name 
                FROM   user_constraints 
                WHERE  constraint_name = uc.r_constraint_name) r_table_name, 
               (SELECT column_name 
                FROM   user_cons_columns 
                WHERE  constraint_name = uc.r_constraint_name 
                       AND position = cols.position)           r_column_name, 
               cols.position, 
               uc.constraint_type 
        FROM   user_constraints uc 
               inner join user_cons_columns cols 
                       ON uc.constraint_name = cols.constraint_name 
        WHERE  constraint_type != 'C') 
START WITH table_name = '&&tableName' 
           AND column_name = '&&columnName' 
CONNECT BY NOCYCLE PRIOR table_name = r_table_name 
                         AND PRIOR column_name = r_column_name; 

Jest to niezwykle przydatne - pokazuje rekurencyjnie wszystkie tabele z określonej tabeli głównej, które zawierają jako klucz wartość kolumny wybranej w tej tabeli głównej. Super, dzięki.
Ev0oD,

To jest naprawdę fajne - dobra robota. Dodałbym tylko lower () w celu porównania nazwa_tabeli i nazwa_kolumny.
Tobias Otto

4

To było w produkcie od lat - chociaż nie było go w produkcie w 2011 roku.

Ale po prostu kliknij stronę Model.

Upewnij się, że masz co najmniej wersję 4.0 (wydaną w 2013 r.), Aby uzyskać dostęp do tej funkcji.

wprowadź opis zdjęcia tutaj


0

Aby dodać do powyższej odpowiedzi dla wtyczki dewelopera sql, użycie poniższego xml pomoże w uzyskaniu powiązania kolumny z kluczem obcym.

    <items>
        <item type="editor" node="TableNode" vertical="true">
        <title><![CDATA[FK References]]></title>
        <query>
            <sql>
                <![CDATA[select a.owner,
                                a.constraint_name,
                                a.table_name,
                                b.column_name,
                                a.status
                         from   all_constraints a
                         join   all_cons_columns b ON b.constraint_name = a.constraint_name
                         where  a.constraint_type = 'R'
                                and exists(
                                   select 1
                                   from   all_constraints
                                   where  constraint_name=a.r_constraint_name
                                          and constraint_type in ('P', 'U')
                                          and table_name = :OBJECT_NAME
                                          and owner = :OBJECT_OWNER)
                                   order by table_name, constraint_name]]>
            </sql>
        </query>
        </item>
    </items>
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.