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-28

Displaying All Schemas Visible on a Node

This example selects information from the CATSYS and SCHEMATA tables to display

a list of all the schemas visible on a node:

>>set schema nonstop_sqlmx_krypton.system_schema;

--- SQL operation complete.

>>select substring(schema_name,1,30) as schema_name,

substring(cat_name,1,20) as cat_name, node_name

from schemata s, catsys c, cat_references cr

where s.cat_uid=c.cat_uid

and c.cat_uid=cr.cat_uid

and cr.replication_rule='A';

SCHEMA_NAME CAT_NAME NODE_NAME

------------------------------ -------------------- ---------

SCH2 CAT \KRYPTON

SCH CAT \KRYPTON

DEFINITION_SCHEMA_VERSION_3000 CAT \KRYPTON

DEFINITION_SCHEMA_VERSION_3000 DMCAT \KRYPTON

WHSE DMCAT \KRYPTON

DEFINITION_SCHEMA_VERSION_3000 NONSTOP_SQLMX_KRYPTO \KRYPTON

SYSTEM_SQLJ_SCHEMA NONSTOP_SQLMX_KRYPTO \KRYPTON

SYSTEM_SCHEMA NONSTOP_SQLMX_KRYPTO \KRYPTON

SYSTEM_DEFAULTS_SCHEMA NONSTOP_SQLMX_KRYPTO \KRYPTON

MXCS_SCHEMA NONSTOP_SQLMX_KRYPTO \KRYPTON

DEFINITION_SCHEMA_VERSION_3000 NONSTOP_SYSTEM_NSK \KRYPTON

PUBLIC_ACCESS_SCHEMA NONSTOP_SYSTEM_NSK \KRYPTON

DEFINITION_SCHEMA_VERSION_3000 PSDB \KRYPTON

PUBS PSDB \KRYPTON

DEFINITION_SCHEMA_VERSION_3000 SAMDBCAT \KRYPTON

SALES SAMDBCAT \KRYPTON

PERSNL SAMDBCAT \KRYPTON

INVENT SAMDBCAT \KRYPTON

--- 18 row(s) selected.