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-39
Determining Whether a Table Has Indexes
Related topics:
•
Displaying All Partitions for a Table or Index on page 8-44
•
Displaying All Columns in an Index on page 8-52
The OBJECTS table is the definition schema table that lists all indexes in a catalog.
The ACCESS_PATHS table, located in the same schema for each catalog, describes
the physical instances of indexes. For detailed information about all metadata tables,
see Table 8-1 on page 8-6 or the SQL/MX Reference Manual.
All queries for information about a specific index must access information from the
CATSYS, SCHEMATA, and OBJECTS tables.
Determining Whether a Table Has Indexes
Use the MXCI SHOWDDL command to display the approximate CREATE TABLE
statement that might have created that table, including the CREATE INDEX statements
for all indexes on the table. To obtain information about indexes, use the MXCI
SHOWLABEL INDEX command. For examples of the SHOWDDL and SHOWLABEL
commands, see the SQL/MX Reference Manual.
This example (when run on the node on which the metadata is located) uses SELECT
statements to help determine whether the table SAMDBCAT.SALES.CUSTOMER has
indexes:
>> set schema samdbcat.definition_schema_version_1200;
--- SQL operation complete.
>> select count(*) as num_indexes
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;