select o.name, c.name, lpad(decode(bitand(ct.flags, 512), 512, 'Y', 'N'), 15)
from sys.coltype$ ct, sys."_CURRENT_EDITION_OBJ" o, sys.col$ c
where o.owner# = userenv('SCHEMAID')
and bitand(ct.flags, 2) = 2 /* ADT column */
and o.obj#=ct.obj#
and o.obj#=c.obj#
and c.intcol#=ct.intcol#
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#=ct.intcol#
and ac.obj#=ct.obj#)
union all
select o.name, ac.name, lpad(decode(bitand(ct.flags, 512), 512, 'Y', 'N'), 15)
from sys.coltype$ ct, sys."_CURRENT_EDITION_OBJ" o, sys.attrcol$ ac, col$ c
where o.owner# = userenv('SCHEMAID')
and bitand(ct.flags, 2) = 2 /* ADT column */
and o.obj#=ct.obj#
and o.obj#=c.obj#
and o.obj#=ac.obj#
and c.intcol#=ct.intcol#
and c.intcol#=ac.intcol#
and bitand(c.property,32768) != 32768 /* not unused column */
SELECT O.NAME
, C.NAME
, LPAD(DECODE(BITAND(CT.FLAGS
, 512)
, 512
, 'Y'
, 'N')
, 15)
FROM SYS.COLTYPE$ CT
, SYS."_CURRENT_EDITION_OBJ" O
, SYS.COL$ C
WHERE O.OWNER# = USERENV('SCHEMAID')
AND BITAND(CT.FLAGS
, 2) = 2 /* ADT COLUMN */
AND O.OBJ#=CT.OBJ#
AND O.OBJ#=C.OBJ#
AND C.INTCOL#=CT.INTCOL#
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#=CT.INTCOL#
AND AC.OBJ#=CT.OBJ#)
UNION ALL
SELECT O.NAME
, AC.NAME
, LPAD(DECODE(BITAND(CT.FLAGS
, 512)
, 512
, 'Y'
, 'N')
, 15)
FROM SYS.COLTYPE$ CT
, SYS."_CURRENT_EDITION_OBJ" O
, SYS.ATTRCOL$ AC
, COL$ C
WHERE O.OWNER# = USERENV('SCHEMAID')
AND BITAND(CT.FLAGS
, 2) = 2 /* ADT COLUMN */
AND O.OBJ#=CT.OBJ#
AND O.OBJ#=C.OBJ#
AND O.OBJ#=AC.OBJ#
AND C.INTCOL#=CT.INTCOL#
AND C.INTCOL#=AC.INTCOL#
AND BITAND(C.PROPERTY
, 32768) != 32768 /* NOT UNUSED COLUMN */
|
|
|