SQL statement used to query the foreign key information of a table's primary key. In my BSOOC, I need an SQL statement to query the foreign key information of the table's primary key. Last night I studied it at AM and finally achieved this goal: Oracle: selecto. obj # asobjectId, o. nameAStableName, oc. n in my BSOOC, I need an SQL statement to query the foreign key information of the table's primary key. last night, I studied it at AM and finally achieved this goal:
Oracle:
Select o. obj # as objectId, o. name AS tableName, oc. name AS constraintName,
Decode (c. type #, 1, 'C', 2, 'P', 3, 'u ',
4, 'R', 5, 'V', 6, 'O', 7, 'C ','? ') As constraintType,
Col. name AS columnName
From sys. con $ oc, sys. con $ rc,
Sys. obj $ ro, sys. obj $ o, sys. obj $ oi,
Sys. cdef $ c,
Sys. col $ col, sys. ccol $ cc, sys. attrcol $ ac
Where oc. con # = c. con #
And c. obj # = o. obj #
And c. rcon # = rc. con #()
And c. enabled = oi. obj #()
And c. robj # = ro. obj #()
And c. type #! = 8
And c. type #! = 12/* don't include log groups */
And c. con # = cc. con #
And cc. obj # = col. obj #
And cc. intcol # = col. intcol #
And cc. obj # = o. obj #
And col. obj # = ac. obj #()
And col. intcol # = ac. intcol #()
And o. name = 'Your table'
SQL Server:
SELECT sysobjects. id objectId,
OBJECT_NAME (sysobjects. parent_obj) tableName,
Sysobjects. name constraintName,
Sysobjects. xtype AS constraintType,
Syscolumns. name AS columnName
FROM sysobjects inner join sysconstraints
ON sysobjects. xtype in ('C', 'F', 'PK', 'uq', 'D ')
AND sysobjects. id = sysconstraints. constid
Left outer join syscolumns ON sysconstraints. id = syscolumns. id
WHERE OBJECT_NAME (sysobjects. parent_obj) = 'Your table'
Other databases have no time to implement.
Explain Oracle: select o. obj # as objectId, o. name AS tableName, oc. n...