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-32
Displaying All Tables in a Schema
DEPT PERSNL
EMPLOYEE PERSNL
HISTOGRAMS PERSNL
HISTOGRAM_INTERVALS PERSNL
JOB PERSNL
PROJECT PERSNL
CUSTOMER SALES
HISTOGRAMS SALES
HISTOGRAM_INTERVALS SALES
ODETAIL SALES
ORDERS SALES
PARTS SALES
Displaying All Tables in a Schema
This example displays all table objects in the schema SALES and catalog SAMDBCAT.
Table objects include base tables, views, MP aliases, and SPJs:
>> set schema samdbcat.definition_schema_version_1200;
--- SQL operation complete.
>> select substring (object_name, 1, 25) as table_name,
object_name_space as name_space,
object_type as type,
case object_security_class
when 'UM' then 'User Metadata'
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'
and o.object_name_space = 'TA'
order by object_name_space, table_name
for read uncommitted access;
TABLE_NAME NAME_SPACE TYPE SEC_CLASS
------------------------- ---------- ---- -----------------
CUSTLIST TA VI User Table
CUSTOMER TA BT User Table
HISTOGRAMS TA BT User Metadata
HISTOGRAM_INTERVALS TA BT User Metadata
ODETAIL TA BT User Table
ORDERS TA BT User Table
ORDREP TA VI User Table
PARTS TA BT User Table