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-40
Displaying All Views in a Schema
Displaying All Views in a Schema
This example displays all views in the schema SALES in the catalog SAMDBCAT:
>>set schema samdbcat.definition_schema_version_<schema
version>;
--- SQL operation complete.
>>select substring (object_name, 1, 25) as view_name,
object_name_space as name_space
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'
and o.object_type = 'VI'
order by object_name
for read uncommitted access;
VIEW_NAME NAME_SPACE
------------------------- ----------
CUSTLIST TA
ORDREP TA
Displaying the Attributes of a View
This example displays all the attributes for views in the schema SALES in the catalog
SAMDBCAT:
>> set schema samdbcat.definition_schema_version_<schema
version>;
--- SQL operation complete.
>> select substring (object_name, 1, 20) as object_name,
check_option, is_updatable, is_insertable
from nonstop_sqlmx_<system name>.system_schema.catsys c,
nonstop_sqlmx_<system name>.system_schema.schemata s,
objects o,
vws v
where c.cat_uid = s.cat_uid
and c.cat_name = 'SAMDBCAT'
and s.schema_uid = o.schema_uid
and s.schema_name = 'SALES'
and o.object_type = 'VI'
and v.object_uid = o.object_uid
order by object_name
for read uncommitted access;
OBJECT_NAME CHECK_OPTION IS_UPDATABLE IS_INSERTABLE
-------------------- ------------ ------------ -------------