SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Querying SQL/MX Metadata
HP NonStop SQL/MX Installation and Management Guide—523723-004
8-43
Displaying the State of Indexes for a Table
Displaying the State of Indexes for a Table
This example displays the state of the indexes for the table
SAMDBCAT.SALES.CUSTOMER:
>> set schema samdbcat.definition_schema_version_1200;
--- SQL operation complete.
>> select
substring (o1.object_name, 1, 40) as index_name,
case a.valid_data
when 'Y' then 'populated'
else 'unpopulated'
end as index_status
from samdbcat.definition_schema_version_1200.access_paths a,
samdbcat.definition_schema_version_1200.objects o1
where
a.access_path_uid = o1.object_uid and
a.access_path_uid <> a.table_uid and
a.table_uid =
(select object_uid from
samdbcat.definition_schema_version_1200.objects o
where
o.object_name = 'CUSTOMER' and
o.schema_uid =
(select schema_uid from
nonstop_sqlmx_figaro.system_schema.schemata s
where s.schema_name = 'SALES' and
s.cat_uid =
(select cat_uid from
nonstop_sqlmx_figaro.system_schema.catsys c
where c.cat_name = 'SAMDBCAT' and
schema_version = 1200
)
)
)
for read uncommitted access;
INDEX_NAME INDEX_STATUS
---------------------------------------- ------------
XCUSTNAM populated