SQL/MX 3.2 Management Manual (H06.25+, J06.14+)

Displaying all Columns in a Referential Integrity Constraint
This example displays all columns with referential integrity constraints for the table
SAMDBCAT.SALES.ODETAIL:
>> alter table samdbcat.sales.odetail add constraint p1
foreign key (partnum)
references samdbcat.sales.parts(partnum);
>> set schema samdbcat.definition_schema_version_<schema version>;
>> select substring(c.column_name, 1, 15) as column_name,
c.column_number as col_num,
r.update_rule,
substring(o2.object_name , 1, 24) as constraint_name
from cols c,
objects o1,
objects o2,
key_col_usage k,
ref_constraints r,
tbl_constraints t
where c.object_uid = t.table_uid
and k.constraint_uid = t.constraint_uid
and r.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_<sys name>.system_schema.schemata s
where s.schema_name = 'SALES' and
s.cat_uid =
(select cat_uid from
nonstop_sqlmx_<sys name>.system_schema.catsys c
where c.cat_name = 'SAMDBCAT' and
schema_version = <schema version>
)
)
for read uncommitted access;
CONSTRAINT_NAME
----------------------
UPDATE_RULE
-----------
COL_NUM
----------
COLUMN_NAME
--------------
P1NA1PARTNUM
Displaying the Attributes of a Column
This example displays some of the attributes of the columns in the table
SAMDBCAT.PERSNL.EMPLOYEE. To display additional attributes, select different fields from the
COLS table:
>> set schema samdbcat.definition_schema_version_<schema version>;
--- SQL operation complete.
>> select substring(c.column_name, 1, 15) as column_name,
c.column_number as col_num,
c.sql_data_type,
case c.column_class
when 'S' then 'SYSTEM'
when 'U' then 'USER'
when 'A' then 'ADDED'
else 'OTHER'
end as col_class
from cols c, objects o
142 Querying SQL/MX Metadata