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-28
Displaying All Objects in a Schema
Displaying All Objects in a Schema
This example selects information from the CATSYS, SCHEMATA, and OBJECTS
tables to display a list of all the objects within the SALES schema in the SAMDBCAT
catalog. You can use a similar set of SELECT statements to display all the views or
indexes on a node.
>> set schema samdbcat.definition_schema_version_1200;
--- SQL operation complete.
>> select substring (object_name, 1, 25) as object_name,
object_name_space as name_space,
object_type as type,
case object_security_class
when 'UT' then 'User Table'
when 'SM' then 'System Metadata'
else 'OTHER'
end as sec_class
from nonstop_sqlmx_figaro.system_schema.catsys c,
nonstop_sqlmx_figaro.system_schema.schemata s,
objects o
where c.cat_uid = s.cat_uid
and c.cat_name = 'SAMDBCAT'
and s.schema_uid = o.schema_uid
and s.schema_name = 'SALES'
order by object_name
for read uncommitted access;
OBJECT_NAME NAME_SPACE TYPE SEC_CLASS
------------------------- ---------- ---- -----------------
CUSTLIST TA VI User Table
CUSTOMER TA BT User Table
CUSTOMER_693278234_9441 CN PK User Table
CUSTOMER_815548234_9441 CN NN User Table
DATE_CONSTRNT CN CC User Table
ODETAIL TA BT User Table
ODETAIL_546467344_9441 CN PK User Table
ODETAIL_824247344_9441 CN NN User Table
ORDERS TA BT User Table
ORDERS_235551434_9441 CN NN User Table
ORDERS_396871434_9441 CN PK User Table
ORDREP TA VI User Table
PARTS TA BT User Table
PARTS_327131544_9441 CN PK User Table
PARTS_926899444_9441 CN NN User Table
XCUSTNAM IX IX User Table
XORDCUS IX IX User Table
XORDREP IX IX User Table
XPARTDES IX IX User Table