SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

Displaying all Tables on a System
Displaying all tables on a node (system) is a two-step process. To display all tables on a node,
you must:
1. Run a query that displays all the catalogs on a node (see “Displaying all Catalogs Visible on
a Node” (page 119)).
2. Run a query for each catalog that displays all the tables for that catalog (see “Displaying all
Tables in a Catalog” (page 125)).
If your database is distributed over multiple nodes, you must run a query for each node that displays
the catalogs on that node.
Displaying Information About Views
Topics in this subsection:
• “Displaying all Views in a Schema” (page 129)
• “Displaying the Attributes of a View” (page 129)
Related topics:
• “Displaying all Columns in a View” (page 139)
• “Displaying all Privileges for a View” (page 145)
The VWS table is the definition schema table that lists all views in a catalog. Text for views is stored
separately in the TEXT table. For more information about all metadata tables, see Figure 3 (page 109)
in this manual or the SQL/MX Reference Manual.
All queries for information about a specific view must access information from the CATSYS,
SCHEMATA, and OBJECTS tables, and from the VWS table.
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;
NAME_SPACE
----------
VIEW_NAME
-------------------------
TA
TA
CUSTLIST
ORDREP
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,
Displaying Information About Views 129