Retrieving materialized view information from the database

  • 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.

    See also Materialized view statuses and properties.

  • 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;

    Caution

    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.

Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1