Список внешних ключей и таблиц, которые они ссылаются

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

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' 

Но мне все еще нужно знать, на какую таблицу и первичный ключ ссылается этот ключ. Как я получу это?

Указанный первичный ключ описан в столбцах r_owner и r_constraint_name таблицы ALL_CONSTRAINTS . Это даст вам информацию, которую вы хотите:

 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 

Попробуй это:

 select * from all_constraints where r_constraint_name in (select constraint_name from all_constraints where table_name='YOUR_TABLE_NAME'); 

Вот универсальный скрипт, который мы используем, который был невероятно удобен.

Сохраните его, чтобы вы могли выполнить его напрямую (@ fkeys.sql). Он позволит вам выполнить поиск по Owner, а также родительскую или дочернюю таблицу и показать отношения внешних ключей. Текущий скрипт явно связывает с C: \ SQLRPTS, поэтому вам нужно будет создать эту папку для изменения этой строки для того, что вы хотите использовать.

 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 

Это перемещает иерархию внешних ключей для заданной таблицы и столбца и возвращает столбцы от ребенка и внука и всех таблиц потомков. Он использует подзапросы для добавления r_table_name и r_column_name в user_constraints, а затем использует их для соединения строк.

 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 = 'MY_TABLE_NAME' and column_name = 'MY_COLUMN_NAME' connect by nocycle prior table_name = r_table_name and prior column_name = r_column_name; 

Вот еще одно решение. Использование представлений sys по умолчанию настолько медленное (примерно 10 секунд в моей ситуации). Это намного быстрее, чем это (около 0,5 с).

 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 */; 

Если вам нужны все foreign keys пользователя, используйте следующий скрипт

 SELECT a.constraint_name, a.table_name, a.column_name, c.owner, c_pk.table_name r_table_name, b.column_name r_column_name FROM user_cons_columns a JOIN user_constraints c ON a.owner = c.owner AND a.constraint_name = c.constraint_name JOIN user_constraints c_pk ON c.r_owner = c_pk.owner AND c.r_constraint_name = c_pk.constraint_name JOIN user_cons_columns b ON C_PK.owner = b.owner AND C_PK.CONSTRAINT_NAME = b.constraint_name AND b.POSITION = a.POSITION WHERE c.constraint_type = 'R' 

основанный на коде Vincent Malgrat

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

 SELECT a.table_name child_table, a.column_name child_column, a.constraint_name, b.table_name parent_table, b.column_name parent_column FROM all_cons_columns a JOIN all_constraints c ON a.owner = c.owner AND a.constraint_name = c.constraint_name join all_cons_columns b on c.owner = b.owner and c.r_constraint_name = b.constraint_name WHERE c.constraint_type = 'R' AND a.table_name = 'your table name' 

В случае, если вы хотите создать ограничения FK из таблицы окружения UAT в Live, выполните пожар ниже динамического запроса …..

  SELECT 'ALTER TABLE '||OBJ.NAME||' ADD CONSTRAINT '||CONST.NAME||' FOREIGN KEY ('||COALESCE(ACOL.NAME, COL.NAME)||') REFERENCES ' ||ROBJ.NAME ||' ('||COALESCE(RACOL.NAME, RCOL.NAME) ||');' 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 AND OBJ.NAME = ; 

Моя версия, по моему скромному мнению, более читаема:

 SELECT PARENT.TABLE_NAME "PARENT TABLE_NAME" , PARENT.CONSTRAINT_NAME "PARENT PK CONSTRAINT" , '->' " " , CHILD.TABLE_NAME "CHILD TABLE_NAME" , CHILD.COLUMN_NAME "CHILD COLUMN_NAME" , CHILD.CONSTRAINT_NAME "CHILD CONSTRAINT_NAME" FROM ALL_CONS_COLUMNS CHILD , ALL_CONSTRAINTS CT , ALL_CONSTRAINTS PARENT WHERE CHILD.OWNER = CT.OWNER AND CT.CONSTRAINT_TYPE = 'R' AND CHILD.CONSTRAINT_NAME = CT.CONSTRAINT_NAME AND CT.R_OWNER = PARENT.OWNER AND CT.R_CONSTRAINT_NAME = PARENT.CONSTRAINT_NAME AND CHILD.TABLE_NAME = ::table -- table name variable AND CT.OWNER = ::owner; -- schema variable, could not be needed 

Его немного поздно, чтобы ответить, но я надеюсь, что мой ответ был полезен для кого-то, кому нужно выбрать Composite foreign keys.

 SELECT "C"."CONSTRAINT_NAME", "C"."OWNER" AS "SCHEMA_NAME", "C"."TABLE_NAME", "COL"."COLUMN_NAME", "REF_COL"."OWNER" AS "REF_SCHEMA_NAME", "REF_COL"."TABLE_NAME" AS "REF_TABLE_NAME", "REF_COL"."COLUMN_NAME" AS "REF_COLUMN_NAME" FROM "USER_CONSTRAINTS" "C" INNER JOIN "USER_CONS_COLUMNS" "COL" ON "COL"."OWNER" = "C"."OWNER" AND "COL"."CONSTRAINT_NAME" = "C"."CONSTRAINT_NAME" INNER JOIN "USER_CONS_COLUMNS" "REF_COL" ON "REF_COL"."OWNER" = "C"."R_OWNER" AND "REF_COL"."CONSTRAINT_NAME" = "C"."R_CONSTRAINT_NAME" AND "REF_COL"."POSITION" = "COL"."POSITION" WHERE "C"."TABLE_NAME" = 'TableName' AND "C"."CONSTRAINT_TYPE" = 'R' 

Я использовал ниже код, и он служил моей цели –

 SELECT fk.owner, fk.table_name, col.column_name FROM dba_constraints pk, dba_constraints fk, dba_cons_columns col WHERE pk.constraint_name = fk.r_constraint_name AND fk.constraint_name = col.constraint_name AND pk.owner = col.owner AND pk.owner = fk.owner AND fk.constraint_type = 'R' AND pk.owner = sys_context('USERENV', 'CURRENT_SCHEMA') AND pk.table_name = :my_table AND pk.constraint_type = 'P'; 
 select d.table_name, d.constraint_name "Primary Constraint Name", b.constraint_name "Referenced Constraint Name" from user_constraints d, (select c.constraint_name, c.r_constraint_name, c.table_name from user_constraints c where table_name='EMPLOYEES' --your table name instead of EMPLOYEES and constraint_type='R') b where d.constraint_name=b.r_constraint_name 
 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 :=TABLE_NAME AND c.owner :=OWNER_NAME; 
 WITH reference_view AS (SELECT a.owner, a.table_name, a.constraint_name, a.constraint_type, a.r_owner, a.r_constraint_name, b.column_name FROM dba_constraints a, dba_cons_columns b WHERE a.owner LIKE UPPER ('SYS') AND a.owner = b.owner AND a.constraint_name = b.constraint_name AND constraint_type = 'R'), constraint_view AS (SELECT a.owner a_owner, a.table_name, a.column_name, b.owner b_owner, b.constraint_name FROM dba_cons_columns a, dba_constraints b WHERE a.owner = b.owner AND a.constraint_name = b.constraint_name AND b.constraint_type = 'P' AND a.owner LIKE UPPER ('SYS') ) SELECT rv.table_name FK_Table , rv.column_name FK_Column , CV.table_name PK_Table , rv.column_name PK_Column , rv.r_constraint_name Constraint_Name FROM reference_view rv, constraint_view CV WHERE rv.r_constraint_name = CV.constraint_name AND rv.r_owner = CV.b_owner; 

Для загрузки UserTable (список внешних ключей и таблиц, которые они ссылаются)

 WITH reference_view AS (SELECT a.owner, a.table_name, a.constraint_name, a.constraint_type, a.r_owner, a.r_constraint_name, b.column_name FROM dba_constraints a, dba_cons_columns b WHERE a.owner = b.owner AND a.constraint_name = b.constraint_name AND constraint_type = 'R'), constraint_view AS (SELECT a.owner a_owner, a.table_name, a.column_name, b.owner b_owner, b.constraint_name FROM dba_cons_columns a, dba_constraints b WHERE a.owner = b.owner AND a.constraint_name = b.constraint_name AND b.constraint_type = 'P' ) , usertableviewlist AS ( select TABLE_NAME from user_tables ) SELECT rv.table_name FK_Table , rv.column_name FK_Column , CV.table_name PK_Table , rv.column_name PK_Column , rv.r_constraint_name Constraint_Name FROM reference_view rv, constraint_view CV , usertableviewlist UTable WHERE rv.r_constraint_name = CV.constraint_name AND rv.r_owner = CV.b_owner And UTable.TABLE_NAME = rv.table_name; 
Interesting Posts

Сообщение об ошибке: не удается найти или открыть файл PDB

Ошибка browser.msie после обновления до jQuery 1.9.1

Почему трассировка блоков стоит дорого?

Создание сочетаний клавиш Spotify

Студия Android Где установить файл NDK? (загрузите его в zip)

Как я могу запустить скрипт каждый день в Mac OS X?

Решение Visual Studio 2015 обеспечивает обратную совместимость с Visual Studio 2013

Форматирование больших валютных или долларовых значений в миллионы / миллиарды

Можно ли использовать ПК в качестве внешнего накопителя через USB?

Windows 8 постоянно воспроизводит звук устройства Connect

Не удалось найти допустимый путь сертификации для запрошенной целевой ошибки даже после импорта сертификата

Каков правильный способ отправки HTTP 404-ответа из действия ASP.NET MVC?

Трекпадный жест для переключения на заголовок / источник

Почему мой OBJ-парсер оказывает такие сетки?

Отключение жестов касания в Windows 8.1

Давайте будем гением компьютера.