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-46
Displaying Constraint Information
Displaying Constraint Information
Topics in this subsection:
•
Determining Whether a Table Has Constraints on page 8-46
•
Displaying All Constraints on a Table on page 8-47
•
Displaying the Attributes of a Constraint on page 8-48
Related topics:
•
Displaying All Columns in a Primary Key or Unique Constraint on page 8-53
•
Displaying All Columns in a NOT NULL Constraint on page 8-54
•
Displaying All Columns in a Referential Integrity Constraint on page 8-55
The TBL_CONSTRAINTS table, located in the schema
DEFINITION_SCHEMA_VERSION_version-number of each catalog, describes all
constraints on a table. 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 partition must access information from the
CATSYS, SCHEMATA, and OBJECTS tables, and from the TBL_CONSTRAINTS
table.
Determining Whether a Table Has Constraints
Use the MXCI SHOWDDL command to display the approximate CREATE TABLE
statement that might have created that 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 counts the number of constraints for the table
SAMDBCAT.SALES.CUSTOMER:
>> set schema samdbcat.definition_schema_version_1200;
--- SQL operation complete.
>> select count(*) as num_constraints
from tbl_constraints t,
objects o
where t.table_uid = o.object_uid
and o.object_name = 'CUSTOMER'
and o.schema_uid =
(select schema_uid
from nonstop_sqlmx_figaro.system_schema.schemata
where
schema_name = 'SALES' and
cat_uid =
(select cat_uid
from nonstop_sqlmx_figaro.system_schema.catsys
where cat_name = 'SAMDBCAT'