Identify All the Foreign Keys in Your Database
The?following?code?enables?you?to?identify?all?of?your?foreign?keys?in?a?particular?database. DUMMY?TEXT:?This?is?some?dummy?text.?Hopefully?there?is?no?issues?in?submitting?this?tip?on?devx.com SELECT?PKTABLE_QUALIFIER?=?CONVERT(SYSNAME,DB_NAME()),? ???????PKTABLE_OWNER?=?CONVERT(SYSNAME,SCHEMA_NAME(O1.SCHEMA_ID)),? ???????PKTABLE_NAME?=?CONVERT(SYSNAME,O1.NAME),? ???????PKCOLUMN_NAME?=?CONVERT(SYSNAME,C1.NAME),? ???????FKTABLE_QUALIFIER?=?CONVERT(SYSNAME,DB_NAME()),? ???????FKTABLE_OWNER?=?CONVERT(SYSNAME,SCHEMA_NAME(O2.SCHEMA_ID)),? ???????FKTABLE_NAME?=?CONVERT(SYSNAME,O2.NAME),? ???????FKCOLUMN_NAME?=?CONVERT(SYSNAME,C2.NAME),? ???????–?Force?the?column?to?be?non-nullable?(see?SQL?BU?325751)? ???????–KEY_SEQ?????????????=?isnull(convert(smallint,k.constraint_column_id),?sysconv(smallint,0)),? ???????UPDATE_RULE?=?CONVERT(SMALLINT,CASE?OBJECTPROPERTY(F.OBJECT_ID,’CnstIsUpdateCascade’)?? ????????????????????????????????????????WHEN?1?THEN?0? ????????????????????????????????????????ELSE?1? ??????????????????????????????????????END),? ???????DELETE_RULE?=?CONVERT(SMALLINT,CASE?OBJECTPROPERTY(F.OBJECT_ID,’CnstIsDeleteCascade’)?? ????????????????????????????????????????WHEN?1?THEN?0? ????????????????????????????????????????ELSE?1? ??????????????????????????????????????END),? ???????FK_NAME?=?CONVERT(SYSNAME,OBJECT_NAME(F.OBJECT_ID)),? ???????PK_NAME?=?CONVERT(SYSNAME,I.NAME),? ???????DEFERRABILITY?=?CONVERT(SMALLINT,7)???? FROM???SYS.ALL_OBJECTS?O1,? ???????SYS.ALL_OBJECTS?O2,? ???????SYS.ALL_COLUMNS?C1,? ???????SYS.ALL_COLUMNS?C2,? ???????SYS.FOREIGN_KEYS?F? ???????INNER?JOIN?SYS.FOREIGN_KEY_COLUMNS?K? ?????????ON?(K.CONSTRAINT_OBJECT_ID?=?F.OBJECT_ID)?