VIEW: SYS.ALL_CONS_OBJ_COLUMNS

Source

View Text - Preformatted

select uc.name, oc.name, c.name, ut.name, ot.name,
       lpad(decode(bitand(sc.flags, 2), 2, 'Y', 'N'), 15)
from sys.user$ uc, sys."_CURRENT_EDITION_OBJ" oc, sys.col$ c, sys."_BASE_USER" ut,
     sys.obj$ ot, sys.subcoltype$ sc
where oc.owner# = uc.user#
  and bitand(sc.flags, 1) = 1      /* Type is specified in the IS OF clause */
  and oc.obj#=sc.obj#
  and oc.obj#=c.obj#
  and c.intcol#=sc.intcol#
  and sc.toid=ot.oid$
  and ot.owner#=ut.user#
  and bitand(c.property,32768) != 32768                /* not unused column */
  and not exists (select null                  /* Doesn't exist in attrcol$ */
                  from sys.attrcol$ ac
                  where ac.intcol#=sc.intcol#
                        and ac.obj#=sc.obj#)
  and (oc.owner# = userenv('SCHEMAID')
       or oc.obj# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               )
            )
       or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -397/* READ ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
                 )
      )
union all
select uc.name, oc.name, ac.name, ut.name, ot.name,
       lpad(decode(bitand(sc.flags, 2), 2, 'Y', 'N'), 15)
from sys.user$ uc, sys."_CURRENT_EDITION_OBJ" oc, sys.col$ c, sys."_BASE_USER" ut,
     sys.obj$ ot, sys.subcoltype$ sc, sys.attrcol$ ac
where oc.owner# = uc.user#
  and bitand(sc.flags, 1) = 1      /* Type is specified in the IS OF clause */
  and oc.obj#=sc.obj#
  and oc.obj#=c.obj#
  and oc.obj#=ac.obj#
  and c.intcol#=sc.intcol#
  and ac.intcol#=sc.intcol#
  and sc.toid=ot.oid$
  and ot.owner#=ut.user#
  and bitand(c.property,32768) != 32768                /* not unused column */
  and (oc.owner# = userenv('SCHEMAID')
       or oc.obj# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               )
            )
       or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -397/* READ ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
                 )
      )

View Text - HTML Formatted

SELECT UC.NAME
, OC.NAME
, C.NAME
, UT.NAME
, OT.NAME
, LPAD(DECODE(BITAND(SC.FLAGS
, 2)
, 2
, 'Y'
, 'N')
, 15) FROM SYS.USER$ UC
, SYS."_CURRENT_EDITION_OBJ" OC
, SYS.COL$ C
, SYS."_BASE_USER" UT
, SYS.OBJ$ OT
, SYS.SUBCOLTYPE$ SC WHERE OC.OWNER# = UC.USER#
AND BITAND(SC.FLAGS
, 1) = 1 /* TYPE IS SPECIFIED IN THE IS OF CLAUSE */
AND OC.OBJ#=SC.OBJ#
AND OC.OBJ#=C.OBJ#
AND C.INTCOL#=SC.INTCOL#
AND SC.TOID=OT.OID$
AND OT.OWNER#=UT.USER#
AND BITAND(C.PROPERTY
, 32768) != 32768 /* NOT UNUSED COLUMN */
AND NOT EXISTS (SELECT NULL /* DOESN'T EXIST IN ATTRCOL$ */
FROM SYS.ATTRCOL$ AC
WHERE AC.INTCOL#=SC.INTCOL#
AND AC.OBJ#=SC.OBJ#)
AND (OC.OWNER# = USERENV('SCHEMAID') OR OC.OBJ# IN (SELECT OA.OBJ#
FROM SYS.OBJAUTH$ OA
WHERE GRANTEE# IN ( SELECT KZSROROL
FROM X$KZSRO ) ) OR /* USER HAS SYSTEM PRIVILEGES */ EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-45 /* LOCK ANY TABLE */
, -47 /* SELECT ANY TABLE */
, -397/* READ ANY TABLE */
, -48 /* INSERT ANY TABLE */
, -49 /* UPDATE ANY TABLE */
, -50 /* DELETE ANY TABLE */) ) ) UNION ALL SELECT UC.NAME
, OC.NAME
, AC.NAME
, UT.NAME
, OT.NAME
, LPAD(DECODE(BITAND(SC.FLAGS
, 2)
, 2
, 'Y'
, 'N')
, 15) FROM SYS.USER$ UC
, SYS."_CURRENT_EDITION_OBJ" OC
, SYS.COL$ C
, SYS."_BASE_USER" UT
, SYS.OBJ$ OT
, SYS.SUBCOLTYPE$ SC
, SYS.ATTRCOL$ AC WHERE OC.OWNER# = UC.USER#
AND BITAND(SC.FLAGS
, 1) = 1 /* TYPE IS SPECIFIED IN THE IS OF CLAUSE */
AND OC.OBJ#=SC.OBJ#
AND OC.OBJ#=C.OBJ#
AND OC.OBJ#=AC.OBJ#
AND C.INTCOL#=SC.INTCOL#
AND AC.INTCOL#=SC.INTCOL#
AND SC.TOID=OT.OID$
AND OT.OWNER#=UT.USER#
AND BITAND(C.PROPERTY
, 32768) != 32768 /* NOT UNUSED COLUMN */
AND (OC.OWNER# = USERENV('SCHEMAID') OR OC.OBJ# IN (SELECT OA.OBJ#
FROM SYS.OBJAUTH$ OA
WHERE GRANTEE# IN ( SELECT KZSROROL
FROM X$KZSRO ) ) OR /* USER HAS SYSTEM PRIVILEGES */ EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-45 /* LOCK ANY TABLE */
, -47 /* SELECT ANY TABLE */
, -397/* READ ANY TABLE */
, -48 /* INSERT ANY TABLE */
, -49 /* UPDATE ANY TABLE */
, -50 /* DELETE ANY TABLE */) ) )