SQL to list the enabled status of all views

[Former Member]

I have been able to successfully recompile most views. However I did this by disabling dependant views. Now I need to list all disabled views and enable them. How can I do this?

[VolkerBarth]

The view status is listed in the system catalog in SYSOBJECT (and not in SYSVIEW, as I had expected...).

A for loop is very handy for listing database objects and manipulate them, as it does hold the cursor open although the DDL statements within the loop do an automatic commit.

The following should do:

begin
   for forLoop as crs cursor for
      select ST.table_name as strViewName
      from systab ST inner join sysobject SO on ST.object_id = SO.object_id
      where ST.table_type = 21 -- i.e. a regular view
          and SO.status = 4 -- a disabled view
   do
      begin
         declare strStmt varchar(1000);
         set strStmt = 'ALTER VIEW ' || strViewName || ' ENABLE;';
         message  strStmt to client; -- test to check the ALTER VIEW statement
         execute immediate strStmt;
      end;
   end for;
end;

Note: If your view have different owners, you will have to need to add the owner name to the ALTER VIEW statement (and therefore extract it from the system catalog like-wise).

---

A further note: If these views are dependent on another, you might need to apply the ALTER VIEW statements in such an order that those views that are dependent on other disabled views are enabled after the latter. (Some ordering by SYSDEPENDENCY will be of help.)