SQL/MX 3.1 Installation and Management Guide (H06.23+, J06.12+)

Querying SQL/MX Metadata
HP NonStop SQL/MX Release 3.1 Installation and Management Guide—663852-001
8-35
Displaying All Tables in a Schema
TRIGGER_USED DEFINITION_SCHEMA_VERSION_3000
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_<schema
version>;
--- 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_<system name>.system_schema.catsys c,