SQL/MX 3.1 Installation and Management Guide (H06.23+, J06.12+)

Querying SQL/MX Metadata
HP NonStop SQL/MX Release 3.1 Installation and Management Guide—663852-001
8-46
Displaying the Attributes of an Index
Displaying the Attributes of an Index
This example (when run on the node on which the metadata is located) displays some
of the attributes for the index SAMDBCAT.SALES.XCUSTNAM:
>> set schema samdbcat.definition_schema_version_<schema
version>;
--- SQL operation complete.
>> select substring(o1.object_name, 1, 15) as index_name,
column_count as columns,
case partitioning_scheme
when 'RP' then 'FIRST KEY'
when 'SP' then 'SIZE'
when 'N' then 'NOT PARTITIONED'
when 'HP' then 'HASH PARTITIONED'
end as PARTITION_SCHEME,
uniques,
explicit
from samdbcat.definition_schema_version_<schema
version>.access_paths a,
samdbcat.definition_schema_version_<schema
version>.objects o1
where
a.access_path_uid = o1.object_uid and
a.access_path_uid <> a.table_uid and
o1.object_name = 'XCUSTNAM' and
o1.object_type = 'IX' and
o1.schema_uid =
(select schema_uid from
nonstop_sqlmx_<sys name>.system_schema.schemata s
where s.schema_name = 'SALES' and
s.cat_uid =
(select cat_uid from
nonstop_sqlmx_<sys name>.system_schema.catsys c
where c.cat_name = 'SAMDBCAT' and
schema_version = <schema version>
)
)
for read uncommitted access;
INDEX_NAME COLUMNS PARTITION_SCHEME UNIQUES EXPLICIT
--------------- ----------- ---------------- ------- --------
XCUSTNAM 1 FIRST KEY N Y