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?
asked 27 Mar '15, 01:04
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.)