Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Reference » System Procedures » System procedures

sa_make_object system procedure Next Page

sa_materialized_view_info system procedure

Returns information about the specified materialized view.


[ view_name
[, owner_name ] ]


If neither view_name nor owner_name are provided, information about all materialized views in the database is returned.

If owner_name is not provided, then only one of materialized views matching the specified view name is described. The procedure requires DBA authority or execute permissions on DBO owned procedures.

The sa_materialized_view_info system procedure returns the following information:

Column nameData typeDescription
OwnerNameCHAR(128)The creator of the view.
ViewNameCHAR(128)The name of the view.

Status information about the view:

  • D - disabled by the user,

  • N - never refreshed

  • E - error during the last refresh attempt

  • F - underlying data has not changed since the last refresh (fresh)

  • S - underlying data has changed since the last refresh (stale)

ViewLastRefreshedTIMESTAMPThe time when the view was last refreshed. This value is NULL if the view has no data (uninitialized).
DataLastModifiedTIMESTAMPFor a stale view, the last time that underlying data was modified.

Information about the availability of the view for use by the optimizer:

  • Y - the view can be used by the optimizer

  • D - use by optimizer disabled

  • N - contains no data because a refresh has not been done or has failed

  • I - the view cannot be used, for some internal reason

  • O - incompatible option value for current connection

This procedure can be useful for determining the list of materialized views that will never be considered by the optimizer because of a problem with the definition of the view. The AvailForOptimization value is I for these materialized views. To learn more about the restrictions for materialized view definition, see Restrictions when managing materialized views.


DBA authority required

Side effects

All metadata for the specified materialized views, and all dependencies, are loaded into the server cache.