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-53
Displaying All Columns in a Primary Key or Unique
Constraint
Displaying All Columns in a Primary Key or Unique Constraint
This example displays all columns with primary key or unique constraints for the table
SAMDBCAT.SALES.ODETAIL; two columns of this table make up the primary key:
>> set schema samdbcat.definition_schema_version_1200;
>> select substring(c.column_name, 1, 15) as column_name,
c.column_number as col_num,
case t.constraint_type
when 'C' then 'CHECK'
when 'F' then 'FOREIGN'
when 'P' then 'PRIMARY KEY'
when 'U' then 'UNIQUE'
else 'UNKNOWN'
end as type,
substring(o2.object_name , 1, 24) as constraint_name
from cols c,
objects o1,
objects o2,
key_col_usage k,
tbl_constraints t
where c.object_uid = t.table_uid
and k.constraint_uid = t.constraint_uid
and k.column_number = c.column_number
and t.table_uid = o1.object_uid
and o1.object_name = 'ODETAIL'
and o2.object_uid = t.constraint_uid
and o1.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;
COLUMN_NAME COL_NUM TYPE CONSTRAINT_NAME
------------- ---------- ----------- -----------------------
ORDERNUM 0 PRIMARY KEY ODETAIL_185886189_4553
PARTNUM 1 PRIMARY KEY ODETAIL_185886189_4553