My BSOOC needs a query table primary key foreign key information SQL, last night to study until 1 o'clock in the morning, finally to achieve 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#!=/* 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 don't have time to implement.