SQL/MX 3.x Installation and Management Guide (H06.22+, J06.11+)

Querying SQL/MX Metadata
HP NonStop SQL/MX Installation and Management Guide—640325-001
8-33
Displaying All Tables in a Schema
VWS DEFINITION_SCHEMA_VERSION_3000
VW_COL_TBL_COLS DEFINITION_SCHEMA_VERSION_3000
VW_COL_USAGE DEFINITION_SCHEMA_VERSION_3000
VW_TBL_USAGE DEFINITION_SCHEMA_VERSION_3000
HISTOGRAMS INVENT
HISTOGRAMS_FREQ_VALS INVENT
HISTOGRAM_INTERVALS INVENT
MVS_TABLE_INFO_UMD INVENT
MVS_UMD INVENT
MVS_USED_UMD INVENT
PARTLOC INVENT
PARTSUPP INVENT
SUPPLIER INVENT
T1 INVENT
DEPT PERSNL
EMPLOYEE PERSNL
HISTOGRAMS PERSNL
HISTOGRAMS_FREQ_VALS PERSNL
HISTOGRAM_INTERVALS PERSNL
JOB PERSNL
MVS_TABLE_INFO_UMD PERSNL
MVS_UMD PERSNL
MVS_USED_UMD PERSNL
PROJECT PERSNL
CUSTOMER SALES
HISTOGRAMS SALES
HISTOGRAMS_FREQ_VALS SALES
HISTOGRAM_INTERVALS SALES
MVS_TABLE_INFO_UMD SALES
MVS_UMD SALES
MVS_USED_UMD 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_3000;
--- 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_dmr15.system_schema.catsys c,
nonstop_sqlmx_dmr15.system_schema.schemata s,
objects o