select t.obj#,
(select value(po) from ku$_partobj_view po
where po.obj_num = t.obj#),
t.property,
(select value(s) from ku$_storage_view s
where t.file# = s.file_num
and t.block# = s.block_num
and t.ts# = s.ts_num),
(select value(s) from ku$_deferred_stg_view s
where s.obj_num = t.obj#),
(select ts.name from ts$ ts where t.ts# = ts.ts#),
(select ts.blocksize from ts$ ts where t.ts# = ts.ts#),
t.pctfree$, t.pctused$, t.initrans, t.maxtrans, t.flags,
cast( multiset(select * from ku$_constraint0_view con
where con.obj_num = t.obj#
and con.contype not in (7,11)
) as ku$_constraint0_list_t
),
cast( multiset(select * from ku$_constraint1_view con
where con.obj_num = t.obj#
) as ku$_constraint1_list_t
),
cast( multiset(select * from ku$_constraint2_view con
where con.obj_num = t.obj#
) as ku$_constraint2_list_t
),
cast( multiset(select * from ku$_pkref_constraint_view con
where con.obj_num = t.obj#
) as ku$_pkref_constraint_list_t
)
from tab$ t where bitand(t.property,64+512) = 0 -- skip IOT and overflow segs
SELECT T.OBJ#
,
(SELECT VALUE(PO)
FROM KU$_PARTOBJ_VIEW PO
WHERE PO.OBJ_NUM = T.OBJ#)
,
T.PROPERTY
,
(SELECT VALUE(S)
FROM KU$_STORAGE_VIEW S
WHERE T.FILE# = S.FILE_NUM
AND T.BLOCK# = S.BLOCK_NUM
AND T.TS# = S.TS_NUM)
,
(SELECT VALUE(S)
FROM KU$_DEFERRED_STG_VIEW S
WHERE S.OBJ_NUM = T.OBJ#)
,
(SELECT TS.NAME
FROM TS$ TS
WHERE T.TS# = TS.TS#)
,
(SELECT TS.BLOCKSIZE
FROM TS$ TS
WHERE T.TS# = TS.TS#)
,
T.PCTFREE$
, T.PCTUSED$
, T.INITRANS
, T.MAXTRANS
, T.FLAGS
,
CAST( MULTISET(SELECT *
FROM KU$_CONSTRAINT0_VIEW CON
WHERE CON.OBJ_NUM = T.OBJ#
AND CON.CONTYPE NOT IN (7
, 11)
) AS KU$_CONSTRAINT0_LIST_T
)
,
CAST( MULTISET(SELECT *
FROM KU$_CONSTRAINT1_VIEW CON
WHERE CON.OBJ_NUM = T.OBJ#
) AS KU$_CONSTRAINT1_LIST_T
)
,
CAST( MULTISET(SELECT *
FROM KU$_CONSTRAINT2_VIEW CON
WHERE CON.OBJ_NUM = T.OBJ#
) AS KU$_CONSTRAINT2_LIST_T
)
,
CAST( MULTISET(SELECT *
FROM KU$_PKREF_CONSTRAINT_VIEW CON
WHERE CON.OBJ_NUM = T.OBJ#
) AS KU$_PKREF_CONSTRAINT_LIST_T
)
FROM TAB$ T
WHERE BITAND(T.PROPERTY
, 64+512) = 0 -- SKIP IOT
AND OVERFLOW SEGS
|
|
|