Progress
SQL-89
Guide and Reference


Schema Tables for Views

Three schema tables store information on views: _View, _View-Col, and _View-Ref. The view schema tables translate all references into fully qualified base table terms.

The _View schema table contains view definitions, including view name, the names of base tables to which the view refers, base table translations of the WHERE and GROUP BY clauses (if included), and the actual text of the view definition from the CREATE VIEW statement. Four privilege fields contain read, write, create, and delete privileges. The _Updatable field indicates whether the view is updatable.

The _View-Col schema table is the equivalent of the _Field schema table. There is a record for each column in the view definition. Each record contains the view name, column name, equivalent base table column or expression, and two privilege fields.

The _View-Ref schema table is a cross-reference table used to connect views with their underlying tables. A record is created in this table for each base table column to which the view refers. This table provides information on which views are defined in terms of other views, as well as which base tables are used. This schema table prevents you from deleting tables, columns, or views that are referred to in view definitions. For example, you can use this table to look up view dependencies to see all the view definitions that refer to a particular view, base table, or column. When operating in a multi-database environment, only the view relationships within a single database are stored in that database’s _View-Ref table. Dependencies on other databases are not maintained.

Table B–3, Table B–4, and Table B–5 list and describe fields that the CREATE VIEW statement creates. Never modify fields in the view schema tables.

Table B–3: Fields in the _View Schema Table
Field
Description
_Auth-id
The owner (creator) of the view.
_View-Name
The name of the view.
_Base-Tables
A list of the base tables referred to directly or indirectly in the FROM clause of the view definition. If the view is defined in terms of other views, these view definitions are resolved to base tables when the CREATE VIEW statement is compiled.
_Where-Cls
The WHERE clause of the view definition, in terms of base tables. All column references are fully qualified and references to other views are resolved when the CREATE VIEW statement is compiled.
_Group-By
The GROUP BY clause of the view definition, also in base table terms. If the view definition contains the CHECK OPTION, the CHECK OPTION WHERE clause is stored here.
_View-Def
The original view definition as entered in the CREATE VIEW statement. This field is included primarily for documentation purposes so you can see the original view definition. Never alter this field. Any quotation marks in the view definition are not included in the _View-Def field. Otherwise, the original text is unchanged, but might be truncated when displayed on the screen.
_Can-Read
_Can-Write
_Can-Create
_Can-Delete
These fields are initialized as base tables and are modified using the GRANT and REVOKE statements.
_Updatable
When the view is created, SQL determines whether it is updatable according to the ANSI standard. Set to YES or NO.

Table B–4: Fields in the _View-Col Schema Table 
Field
Description
_Auth-id
The owner (creator) of the view.
_View-Name
The name of the view.
_Col-Name
The name of the column. Can be specified explicitly in the view definition, or, if no column list is included, is the same as the name of the corresponding column in the SELECT list.
_Base-Col
The fully qualified base table column name or expression to that this view column references. Fully resolved when the CREATE VIEW statement is compiled.
_Can-Write
Initialized to the owner in the CREATE VIEW statement. You can modify this value using the GRANT and REVOKE statements.
_Can-Create
Reserved for future use.
_Vcol-Order
Provides the default, left-to-right display order of the columns. The lowest number represents the leftmost column; the highest number represents the rightmost column.

Table B–5: Fields in the _View-Ref Schema Table 
Field
Description
_Auth-id
The owner (creator) of the view.
_View-Name
The name of the view whose definition contains the reference.
_Ref-Table
The referenced table or view. This field is not resolved to the base table if the immediate reference is to a view.
_Base-Col
The referenced base table column. This field is resolved to the base table, even if the immediate reference is to a view.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095