Jeg prøver å finne en spørring som vil gi meg en liste over fremmednøklene for en tabell og tabellene og kolonnene de refererer til. Jeg har kommet halvveis med
SELECT a.table_name,
a.column_name,
a.constraint_name,
c.owner
FROM ALL_CONS_COLUMNS A, ALL_CONSTRAINTS C
where A.CONSTRAINT_NAME = C.CONSTRAINT_NAME
and a.table_name=:TableName
and C.CONSTRAINT_TYPE = 'R'
Men jeg trenger fortsatt å vite hvilken tabell og primærnøkkel denne nøkkelen refererer til. Hvordan får jeg vite det?
Den refererte primærnøkkelen er beskrevet i kolonnene r_owner
og r_constraint_name
i tabellen ALL_CONSTRAINTS
. Dette vil gi deg den informasjonen du ønsker:
SELECT a.table_name, a.column_name, a.constraint_name, c.owner,
-- referenced pk
c.r_owner, c_pk.table_name r_table_name, c_pk.constraint_name r_pk
FROM all_cons_columns a
JOIN all_constraints c ON a.owner = c.owner
AND a.constraint_name = c.constraint_name
JOIN all_constraints c_pk ON c.r_owner = c_pk.owner
AND c.r_constraint_name = c_pk.constraint_name
WHERE c.constraint_type = 'R'
AND a.table_name = :TableName
Her er et universalskript som vi bruker, og som har vært utrolig nyttig.
Lagre det slik at du kan kjøre det direkte (@fkeys.sql). Med dette skriptet kan du søke etter Owner og enten Parent- eller Child-tabellen og vise relasjoner med fremmednøkler. Det nåværende skriptet spooler eksplisitt til C:\SQLRPTS, så du må opprette den mappen eller endre den linjen til noe du vil bruke.
REM ########################################################################
REM ##
REM ## fkeys.sql
REM ##
REM ## Displays the foreign key relationships
REM ##
REM #######################################################################
CLEAR BREAK
CLEAR COL
SET LINES 200
SET PAGES 54
SET NEWPAGE 0
SET WRAP OFF
SET VERIFY OFF
SET FEEDBACK OFF
break on table_name skip 2 on constraint_name on r_table_name skip 1
column CHILDCOL format a60 head 'CHILD COLUMN'
column PARENTCOL format a60 head 'PARENT COLUMN'
column constraint_name format a30 head 'FK CONSTRAINT NAME'
column delete_rule format a15
column bt noprint
column bo noprint
TTITLE LEFT _DATE CENTER 'FOREIGN KEY RELATIONSHIPS ON &new_prompt' RIGHT 'PAGE:'FORMAT 999 SQL.PNO SKIP 2
SPOOL C:\SQLRPTS\FKeys_&new_prompt
ACCEPT OWNER_NAME PROMPT 'Enter Table Owner (or blank for all): '
ACCEPT PARENT_TABLE_NAME PROMPT 'Enter Parent Table or leave blank for all: '
ACCEPT CHILD_TABLE_NAME PROMPT 'Enter Child Table or leave blank for all: '
select b.owner || '.' || b.table_name || '.' || b.column_name CHILDCOL,
b.position,
c.owner || '.' || c.table_name || '.' || c.column_name PARENTCOL,
a.constraint_name,
a.delete_rule,
b.table_name bt,
b.owner bo
from all_cons_columns b,
all_cons_columns c,
all_constraints a
where b.constraint_name = a.constraint_name
and a.owner = b.owner
and b.position = c.position
and c.constraint_name = a.r_constraint_name
and c.owner = a.r_owner
and a.constraint_type = 'R'
and c.owner like case when upper('&OWNER_NAME') is null then '%'
else upper('&OWNER_NAME') end
and c.table_name like case when upper('&PARENT_TABLE_NAME') is null then '%'
else upper('&PARENT_TABLE_NAME') end
and b.table_name like case when upper('&CHILD_TABLE_NAME') is null then '%'
else upper('&CHILD_TABLE_NAME') end
order by 7,6,4,2
/
SPOOL OFF
TTITLE OFF
SET FEEDBACK ON
SET VERIFY ON
CLEAR BREAK
CLEAR COL
SET PAGES 24
SET LINES 100
SET NEWPAGE 1
UNDEF OWNER
Her er en annen løsning. Bruk av sys's standardvisninger er så treg (ca. 10s i min situasjon). Dette er mye raskere enn det (ca. 0,5 s).
SELECT
CONST.NAME AS CONSTRAINT_NAME,
RCONST.NAME AS REF_CONSTRAINT_NAME,
OBJ.NAME AS TABLE_NAME,
COALESCE(ACOL.NAME, COL.NAME) AS COLUMN_NAME,
CCOL.POS# AS POSITION,
ROBJ.NAME AS REF_TABLE_NAME,
COALESCE(RACOL.NAME, RCOL.NAME) AS REF_COLUMN_NAME,
RCCOL.POS# AS REF_POSITION
FROM SYS.CON$ CONST
INNER JOIN SYS.CDEF$ CDEF ON CDEF.CON# = CONST.CON#
INNER JOIN SYS.CCOL$ CCOL ON CCOL.CON# = CONST.CON#
INNER JOIN SYS.COL$ COL ON (CCOL.OBJ# = COL.OBJ#) AND (CCOL.INTCOL# = COL.INTCOL#)
INNER JOIN SYS.OBJ$ OBJ ON CCOL.OBJ# = OBJ.OBJ#
LEFT JOIN SYS.ATTRCOL$ ACOL ON (CCOL.OBJ# = ACOL.OBJ#) AND (CCOL.INTCOL# = ACOL.INTCOL#)
INNER JOIN SYS.CON$ RCONST ON RCONST.CON# = CDEF.RCON#
INNER JOIN SYS.CCOL$ RCCOL ON RCCOL.CON# = RCONST.CON#
INNER JOIN SYS.COL$ RCOL ON (RCCOL.OBJ# = RCOL.OBJ#) AND (RCCOL.INTCOL# = RCOL.INTCOL#)
INNER JOIN SYS.OBJ$ ROBJ ON RCCOL.OBJ# = ROBJ.OBJ#
LEFT JOIN SYS.ATTRCOL$ RACOL ON (RCCOL.OBJ# = RACOL.OBJ#) AND (RCCOL.INTCOL# = RACOL.INTCOL#)
WHERE CONST.OWNER# = userenv('SCHEMAID')
AND RCONST.OWNER# = userenv('SCHEMAID')
AND CDEF.TYPE# = 4 /* 'R' Referential/Foreign Key */;