VIEW: SYS.DBA_REFS

Source

View Text - Preformatted

select distinct u.name, o.name,
       decode(bitand(c.property, 1), 1, ac.name, c.name),
       decode(bitand(rc.reftyp, 2), 2, 'YES', 'NO'),
       decode(bitand(rc.reftyp, 1), 1, 'YES', 'NO'),
       su.name, so.name,
       case
         when bitand(reftyp,4) = 4 then 'USER-DEFINED'
         when bitand(reftyp, 8) = 8 then 'SYSTEM GENERATED AND USER-DEFINED'
         else 'SYSTEM GENERATED'
       end
from sys.obj$ o, sys.col$ c, sys.user$ u, sys.refcon$ rc, sys.obj$ so,
     sys.user$ su, sys.attrcol$ ac
where o.owner# = u.user#
  and o.obj# = c.obj#
  and c.obj# = rc.obj#
  and c.col# = rc.col#
  and c.intcol# = rc.intcol#
  and rc.stabid = so.oid$(+)
  and so.owner# = su.user#(+)
  and c.obj# = ac.obj#(+)
  and c.intcol# = ac.intcol#(+)
  and bitand(c.property,32768) != 32768           /* not unused column */

View Text - HTML Formatted

SELECT DISTINCT U.NAME
, O.NAME
, DECODE(BITAND(C.PROPERTY
, 1)
, 1
, AC.NAME
, C.NAME)
, DECODE(BITAND(RC.REFTYP
, 2)
, 2
, 'YES'
, 'NO')
, DECODE(BITAND(RC.REFTYP
, 1)
, 1
, 'YES'
, 'NO')
, SU.NAME
, SO.NAME
, CASE WHEN BITAND(REFTYP
, 4) = 4 THEN 'USER-DEFINED' WHEN BITAND(REFTYP
, 8) = 8 THEN 'SYSTEM GENERATED
AND USER-DEFINED' ELSE 'SYSTEM GENERATED' END FROM SYS.OBJ$ O
, SYS.COL$ C
, SYS.USER$ U
, SYS.REFCON$ RC
, SYS.OBJ$ SO
, SYS.USER$ SU
, SYS.ATTRCOL$ AC WHERE O.OWNER# = U.USER#
AND O.OBJ# = C.OBJ#
AND C.OBJ# = RC.OBJ#
AND C.COL# = RC.COL#
AND C.INTCOL# = RC.INTCOL#
AND RC.STABID = SO.OID$(+)
AND SO.OWNER# = SU.USER#(+)
AND C.OBJ# = AC.OBJ#(+)
AND C.INTCOL# = AC.INTCOL#(+)
AND BITAND(C.PROPERTY
, 32768) != 32768 /* NOT UNUSED COLUMN */