Force recompile of all views

[Former Member]

We recently went through our databases and updated some kernel fields lengths. This we now find out makes basically all our views invalid. How do we force recompiling of all our views?

[VolkerBarth]

What SQL Anywhere version do you use? AFAIK, starting with v10, the database engine tracks view dependencies and therefore should automatically recompile all dependent views -cf. that doc topic for v12:

Dependencies and schema-altering changes

For older versions, AFAIK there was no RECOMPILE clause in the ALTER VIEW statement, so you would have to use ALTER VIEW ... AS with the full select statement.

[Former Member · Accepted solution]

The dbunload utility uses sa_recompile_views. While it is not officially recommended to use this system procedure outside of the reload.sql script it may be helpful to test your changes and to find views that were negatively impacted by your schema changes.

[Former Member]

Hi Logan, in my application I put a piece of code where i found all invalid view and then put them to enable doing 2 or 3 test to consider nested view. Here an extract:

TO FIND INVALID VIEW:

select table_name from SYS.SYSTABLE, SYS.SYSOBJECT where SYS.SYSTABLE.OBJECT_ID = SYS.SYSOBJECT.OBJECT_ID and SYS.SYSOBJECT.STATUS = 2 and table_type = 'VIEW' and table_name > '' ORDER BY table_name

THEN : ROUND 1 FOR EACH ls_sql = "ALTER VIEW dba." + ls_table + " ENABLE " execute immediate :ls_sql

ROUND 2 FOR EACH ls_sql = "ALTER VIEW dba." + ls_table + " ENABLE " execute immediate :ls_sql

ROUND 3 FOR EACH ls_sql = "ALTER VIEW dba." + ls_table + " ENABLE " execute immediate :ls_sql