Status and property information You can request information, such as the status of a materialized view, using the sa_materialized_view_info system procedure. See sa_materialized_view_info system procedure.
Database option information You can retrieve the database options that were stored with a materialized view when it was created by querying the SYSMVOPTION system view. The following statements create a materialized view and then query the database to find out the database options used when creating the view.
CREATE MATERIALIZED VIEW EmployeeConfid15 AS SELECT EmployeeID, Employees.DepartmentID, SocialSecurityNumber, Salary, ManagerID, Departments.DepartmentName, Departments.DepartmentHeadID FROM Employees, Departments WHERE Employees.DepartmentID=Departments.DepartmentID; SELECT option_name, option_value FROM SYSMVOPTION JOIN SYSMVOPTIONNAME WHERE SYSMVOPTION.view_object_id=( SELECT object_id FROM SYSTAB WHERE table_name='EmployeeConfid15' ) ORDER BY option_name;
When you are done with this example, you should drop the materialized view you created. Otherwise, you will not be able to make schema changes to its underlying tables Employees and Departments, when trying out other examples. You cannot alter the schema of a table that has enabled, dependent materialized view. See Drop materialized views.
Dependency information To determine the list of views dependent on a materialized view, use the sa_dependent_views system procedure. See sa_dependent_views system procedure.
This information can also be found in the SYSDEPENDENCY system view. See SYSDEPENDENCY system view.
|Discuss this page in DocCommentXchange.
Send feedback about this page using email.
|Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1|