SQL/MP Installation and Management Guide

Querying SQL/MP Catalogs
HP NonStop SQL/MP Installation and Management Guide—523353-004
6-8
Displaying Information About Indexes
This example selects the help text for the column LAST_NAME from the table
EMPLOYEE:
>> VOLUME \SYS1.$VOL1.PERSNL;
>> SELECT * FROM COMMENTS
+> WHERE OBJNAME = "\SYS1.VOL1.PERSNL.EMPLOYEE" AND
+> OBJSUBNAME = "LAST_NAME" AND
+> OBJTYPE = "HC";
Displaying Information About Indexes
Three catalog tables contain information about keys and indexes:
•
KEYS describes the columns of each primary key and index.
•
INDEXES describes index-file information and includes an entry for the primary
key of each base table.
•
FILES describes file-label information for each index, such as the file type, number
of extents, audit flag, partitioned flag, end-of-file address, and record size.
This example displays values from the TABLENAME column of the CUSTOMER table:
>> VOLUME \SYS1.$VOL1.SALES;
>> SELECT TABLENAME
+> FROM BASETABS
+> WHERE TABLENAME = "\SYS1.$VOL1.SALES.CUSTOMER";
This example displays all indexes for the table CUSTOMER that are described in the
catalog \SYS1.$VOL1.SALES:
>> VOLUME \SYS1.$VOL1.SALES;
>> SELECT * FROM INDEXES
+> WHERE TABLENAME = "\SYS1.$VOL1.SALES.CUSTOMER";
This example displays key information for the table CUSTOMER in a joined view of the
INDEXES and KEYS catalog tables:
>> VOLUME \SYS1.$VOL1.SALES;
>> SELECT K.INDEXNAME, KEYSEQNUMBER, TABLECOLNUMBER, ORDERING
+> FROM KEYS K, INDEXES I
+> WHERE TABLENAME = "\SYS1.$VOL1.SALES.CUSTOMER" AND
+> K.INDEXNAME = I.INDEXNAME;