VIEW: SYS.USER_IND_COLUMNS

Source

View Text - Preformatted

select idx.name, base.name,
       decode(bitand(c.property, 1024), 1024,
              (select decode(bitand(tc.property, 1), 1, ac.name, tc.name)
              from sys.col$ tc, attrcol$ ac
              where tc.intcol# = c.intcol#-1
                and tc.obj# = c.obj#
                and tc.obj# = ac.obj#(+)
                and tc.intcol# = ac.intcol#(+)),
              decode(ac.name, null, c.name, ac.name)),
       ic.pos#, c.length, c.spare3,
       decode(bitand(c.property, 131072), 131072, 'DESC', 'ASC')
from sys.col$ c, sys.obj$ idx, sys.obj$ base, sys.icol$ ic, sys.ind$ i,
       sys.attrcol$ ac
where c.obj# = base.obj#
  and ic.bo# = base.obj#
  and decode(bitand(i.property,1024),0,ic.intcol#,ic.spare2) = c.intcol#
  and base.owner# = userenv('SCHEMAID')
  and base.namespace in (1, 5) /* table or cluster namespace */
  and ic.obj# = idx.obj#
  and idx.obj# = i.obj#
  and i.type# in (1, 2, 3, 4, 6, 7, 9)
  and c.obj# = ac.obj#(+)
  and c.intcol# = ac.intcol#(+)
union all
select idx.name, base.name,
       decode(bitand(c.property, 1024), 1024,
              (select decode(bitand(tc.property, 1), 1, ac.name, tc.name)
               from sys.col$ tc, attrcol$ ac
               where tc.intcol# = c.intcol#-1
                 and tc.obj# = c.obj#
                 and tc.obj# = ac.obj#(+)
                 and tc.intcol# = ac.intcol#(+)),
              decode(ac.name, null, c.name, ac.name)),
       ic.pos#, c.length, c.spare3,
       decode(bitand(c.property, 131072), 131072, 'DESC', 'ASC')
from sys.col$ c, sys.obj$ idx, sys.obj$ base, sys.icol$ ic, sys.ind$ i,
       sys.attrcol$ ac
where c.obj# = base.obj#
  and i.bo# = base.obj#
  and base.owner# != userenv('SCHEMAID')
  and decode(bitand(i.property,1024),0,ic.intcol#,ic.spare2) = c.intcol#
  and idx.owner# = userenv('SCHEMAID')
  and idx.namespace = 4 /* index namespace */
  and ic.obj# = idx.obj#
  and idx.obj# = i.obj#
  and i.type# in (1, 2, 3, 4, 6, 7, 9)
  and c.obj# = ac.obj#(+)
  and c.intcol# = ac.intcol#(+)

View Text - HTML Formatted

SELECT IDX.NAME
, BASE.NAME
, DECODE(BITAND(C.PROPERTY
, 1024)
, 1024
, (SELECT DECODE(BITAND(TC.PROPERTY
, 1)
, 1
, AC.NAME
, TC.NAME)
FROM SYS.COL$ TC
, ATTRCOL$ AC
WHERE TC.INTCOL# = C.INTCOL#-1
AND TC.OBJ# = C.OBJ#
AND TC.OBJ# = AC.OBJ#(+)
AND TC.INTCOL# = AC.INTCOL#(+))
, DECODE(AC.NAME
, NULL
, C.NAME
, AC.NAME))
, IC.POS#
, C.LENGTH
, C.SPARE3
, DECODE(BITAND(C.PROPERTY
, 131072)
, 131072
, 'DESC'
, 'ASC') FROM SYS.COL$ C
, SYS.OBJ$ IDX
, SYS.OBJ$ BASE
, SYS.ICOL$ IC
, SYS.IND$ I
, SYS.ATTRCOL$ AC WHERE C.OBJ# = BASE.OBJ#
AND IC.BO# = BASE.OBJ#
AND DECODE(BITAND(I.PROPERTY
, 1024)
, 0
, IC.INTCOL#
, IC.SPARE2) = C.INTCOL#
AND BASE.OWNER# = USERENV('SCHEMAID')
AND BASE.NAMESPACE IN (1
, 5) /* TABLE OR CLUSTER NAMESPACE */
AND IC.OBJ# = IDX.OBJ#
AND IDX.OBJ# = I.OBJ#
AND I.TYPE# IN (1
, 2
, 3
, 4
, 6
, 7
, 9)
AND C.OBJ# = AC.OBJ#(+)
AND C.INTCOL# = AC.INTCOL#(+) UNION ALL SELECT IDX.NAME
, BASE.NAME
, DECODE(BITAND(C.PROPERTY
, 1024)
, 1024
, (SELECT DECODE(BITAND(TC.PROPERTY
, 1)
, 1
, AC.NAME
, TC.NAME)
FROM SYS.COL$ TC
, ATTRCOL$ AC
WHERE TC.INTCOL# = C.INTCOL#-1
AND TC.OBJ# = C.OBJ#
AND TC.OBJ# = AC.OBJ#(+)
AND TC.INTCOL# = AC.INTCOL#(+))
, DECODE(AC.NAME
, NULL
, C.NAME
, AC.NAME))
, IC.POS#
, C.LENGTH
, C.SPARE3
, DECODE(BITAND(C.PROPERTY
, 131072)
, 131072
, 'DESC'
, 'ASC') FROM SYS.COL$ C
, SYS.OBJ$ IDX
, SYS.OBJ$ BASE
, SYS.ICOL$ IC
, SYS.IND$ I
, SYS.ATTRCOL$ AC WHERE C.OBJ# = BASE.OBJ#
AND I.BO# = BASE.OBJ#
AND BASE.OWNER# != USERENV('SCHEMAID')
AND DECODE(BITAND(I.PROPERTY
, 1024)
, 0
, IC.INTCOL#
, IC.SPARE2) = C.INTCOL#
AND IDX.OWNER# = USERENV('SCHEMAID')
AND IDX.NAMESPACE = 4 /* INDEX NAMESPACE */
AND IC.OBJ# = IDX.OBJ#
AND IDX.OBJ# = I.OBJ#
AND I.TYPE# IN (1
, 2
, 3
, 4
, 6
, 7
, 9)
AND C.OBJ# = AC.OBJ#(+)
AND C.INTCOL# = AC.INTCOL#(+)