SQL/MP Installation and Management Guide

Querying SQL/MP Catalogs
HP NonStop SQL/MP Installation and Management Guide—523353-004
6-6
Displaying Information About Constraints
This example displays selected columns from a join of the TABLES and VIEWS
catalog tables for group ID 240:
>> VOLUME \SYS1.$VOL1.PERSNL;
>> SELECT VIEWNAME, GROUPID, USERID, SECURITYVECTOR,
+>> AUDIT, PROTECTION, WITHCHECKOPTION
+> FROM TABLES, VIEWS
+> WHERE VIEWNAME = TABLENAME AND
+> GROUPID = 240;
Displaying Information About Constraints
Information about constraints on a table appears in the CONSTRNT catalog table in
the catalog that contains the table description.
To determine whether a table has constraints, you can query the BASETABS table to
check whether the constraints flag is set. If CONSTRAINTS is Y, one or more
constraints exist. If CONSTRAINTS is N, no constraint exists.
This example displays the TABLENAME and CONSTRAINTS columns:
>> VOLUME \SYS1.$VOL1.SALES;
>> SELECT TABLENAME, CONSTRAINTS
+> FROM BASETABS
+> WHERE TABLENAME = "\SYS1.$VOL1.SALES.ORDERS";
The default width for displaying a constraint definition (with varying-length data) is 80
characters. For example, you can use the SET STYLE command to specify a
VARCHAR display width of 255 characters.
This example displays all the current constraints on a table:
>> VOLUME \SYS1.$VOL1.SALES;
>> SET STYLE VARCHAR_WIDTH 255;
>> SELECT * FROM CONSTRNT
+> WHERE TABLENAME = "\SYS1.$VOL1.SALES.ORDERS";
Displaying Information About Collations
Catalogs have two tables that describe collations:
•
CPRULES describes characteristics of collations, including whether the collation
ever considers two different characters as being equal (for example, whether ‘A’ =
‘a’), the character set to which the collation rules apply, and the size and version of
the collation.
•
CPRLSRCE describes the collation source definition, including the entire source
text that specifies the character processing rules.
This example displays CPRULES information about a collation:
>> VOLUME \SYS1.$VOL1.SALES;
>> SELECT * FROM CPRULES
+> WHERE CPRULESNAME = "\SYS1.$VOL1.SALES.FRENCH";