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-30
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_<schema
version>;
--- 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_<system name>.system_schema.catsys c,
nonstop_sqlmx_<system name>.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_412443172_5188 CN NN User Table
CUSTOMER_817253172_5188 CN PK User Table
DATE_CONSTRNT CN CC User Table
HISTOGRAMS TA BT OTHER
HISTOGRAMS_249558842_5188 CN PK OTHER
HISTOGRAMS_948848842_5188 CN NN OTHER
HISTOGRAMS_FREQ_VALS TA BT OTHER
HISTOGRAMS_FREQ_VALS_3649 CN PK OTHER
HISTOGRAMS_FREQ_VALS_8547 CN NN OTHER
HISTOGRAM_INTERVALS TA BT OTHER
HISTOGRAM_INTERVALS_62116 CN NN OTHER
HISTOGRAM_INTERVALS_83646 CN PK OTHER
MVS_TABLE_INFO_UMD TA BT OTHER
MVS_TABLE_INFO_UMD_231635 CN NN OTHER
MVS_TABLE_INFO_UMD_782835 CN PK OTHER
MVS_UMD TA BT OTHER
MVS_UMD_326725152_5188 CN NN OTHER
MVS_UMD_462235152_5188 CN PK OTHER
MVS_USED_UMD TA BT OTHER
MVS_USED_UMD_251445152_51 CN NN OTHER