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

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_figaro.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

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

DEFINITION_SCHEMA_VERSION_1200 NONSTOP_SQLMX_FIGARO \FIGARO

SYSTEM_SQLJ_SCHEMA NONSTOP_SQLMX_FIGARO \FIGARO

SYSTEM_SCHEMA NONSTOP_SQLMX_FIGARO \FIGARO

SYSTEM_DEFAULTS_SCHEMA NONSTOP_SQLMX_FIGARO \FIGARO

MXCS_SCHEMA NONSTOP_SQLMX_FIGARO \FIGARO

DEFINITION_SCHEMA_VERSION_1200 CAT \FIGARO

SCH CAT \FIGARO

DEFINITION_SCHEMA_VERSION_1200 SAMDBCAT \FIGARO

SALES SAMDBCAT \FIGARO

PERSNL SAMDBCAT \FIGARO

INVENT SAMDBCAT \FIGARO