SQL/MP Installation and Management Guide

Querying SQL/MP Catalogs
HP NonStop SQL/MP Installation and Management Guide—523353-004
6-5
Displaying Information About Views
This example displays tables and views described in TABLES for a given owner ID. For
this example, the group ID is 240, and the user ID is 100.
>> VOLUME \SYS1.$VOL1.SALES;
>> SELECT TABLENAME, TABLETYPE
+> FROM TABLES
+> WHERE GROUPID = 240 AND USERID = 100;
This example displays selected columns from a join of the TABLES and FILES catalog
tables. The search condition for the join operation is WHERE TABLENAME =
FILENAME.
>> VOLUME \SYS1.$VOL1.SALES;
>> SELECT TABLENAME, TABLETYPE, GROUPID, USERID,
+> SECURITYVECTOR, AUDIT, FILETYPE, EOF
+> FROM TABLES, FILES
+> WHERE TABLENAME = FILENAME;
Displaying Information About Views
Catalogs have two tables that describe views:
•
VIEWS describes the attributes of a view, such as the text of the view definition
and whether the view is a protection or shorthand view.
•
TABLES describes some file-label information, such as the security string, owner
ID, creation timestamp, and number of columns.
You can query each catalog table separately, or you can create a joined view of the
tables.
The default width for displaying view text (with varying-length data) is 80 characters.
For example, you can use the SET STYLE command to specify a VARCHAR display
width of 255 characters. This width ensures that you can display 255 characters of the
view text.
This example displays VIEWS information for a view:
>> VOLUME \SYS1.$VOL1.PERSNL;
>> SET STYLE VARCHAR_WIDTH 255;
>> SELECT * FROM VIEWS
+> WHERE VIEWNAME = "\SYS1.$VOL1.PERSNL.EMPLIST";
This example displays TABLES information for a view:
>> VOLUME \SYS1.$VOL1.PERSNL;
>> SELECT * FROM TABLES
+> WHERE TABLENAME = "\SYS1.$VOL1.PERSNL.EMPLIST";
This example displays view names and the audit flag settings of all views. The
VALIDDEF column of the display shows Y or N to indicate the validity of a view.
>> VOLUME \SYS1.$VOL1.PERSNL;
>> SELECT VIEWNAME, VALIDDEF, AUDIT
+> FROM VIEWS;