Skip to main content

Listing View Properties

Listing View Properties

The INFORMATION.SCHEMA.VIEWSOpens in a new tab persistent class displays information about all views in the current namespace. It provides a number of properties including the view definition, the owner of the view, and the timestamps when the view was created and last modified. These properties also include whether the view is updateable and if so, whether it was defined with a check option.

When specified in Embedded SQL, INFORMATION.SCHEMA.VIEWSOpens in a new tab requires the #include %occInclude macro preprocessor directive. This directive is not required for Dynamic SQL.

The VIEWDEFINITIONOpens in a new tab property (SqlFieldName = VIEW_DEFINITION) returns as a string the view field names and the view’s query expression for all views in the current namespace. For example,

SELECT View_Definition FROM INFORMATION_SCHEMA.VIEWS

returns strings such as: "(vName,vAge) SELECT Name,Age FROM Sample.Person WHERE Age > 21". When issued from the Management Portal SQL Execute Query interface, display of this string is limited to the first 100 characters with whitespace and line breaks removed and (if necessary) an appended ellipsis (...) indicating truncated content. Otherwise, issuing this query returns a string of up to 1048576 characters for each view, with a line break between the view fields list and the query text, with the whitespace specified in the view’s query expression preserved, and (if necessary) an appended ellipsis (...) indicating truncated content.

The following example returns the view name (Table_Name field) and owner name for all views in the current namespace:

SELECT Table_Name,Owner FROM INFORMATION_SCHEMA.VIEWS

The following example returns all information for all non-system views in the current namespace:

SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE Owner != '_SYSTEM'

The INFORMATION.SCHEMA.VIEWCOLUMNUSAGEOpens in a new tab persistent class displays the names of the source table fields for each of the views in the current namespace:

SELECT * FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE WHERE View_Name='MyView'

You can display much of the same information as INFORMATION.SCHEMA.VIEWSOpens in a new tab for a single view using the Catalog Details tab in the Management Portal SQL Interface. The Catalog Details for a view include the definition of each view field (data type, max length, minval/maxval, etc.), details that are not provided by the INFORMATION.SCHEMA view classes. The Catalog Details View Info display also provides an option to edit the view definition.

FeedbackOpens in a new tab